[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: RE: AW: rationalizing multiple ids for the same name

From   "Nick Cox" <>
To   <>
Subject   st: RE: AW: rationalizing multiple ids for the same name
Date   Tue, 18 Aug 2009 17:04:13 +0100

In addition to this good advice, see 

SJ-8-3  dm0039  . . .  Stata tip 64: Cleaning up user-entered string
        . . . . . . . . . . . . . . . . . . . . . . . .  J. Herrin and
E. Poen
        Q3/08   SJ 8(3):444--445                                 (no
        tip on how to clean up user-entered string variables

The bad news is that there is no magic bullet for these problems. How
could there be? The ideal is that Stata knows what you know, that
certain differences are trivial and others definitely are not. How could
what you know be expressed concisely in one or even a few lines of

The good news is that there is a rich variety of string functions that
can help. 


Martin Weiss

Everything rides on what "the same name" means: Sometimes there is an
at the end, sometimes not. If you are willing to assume that some part
the string for "Name" needs to match, you can use the function
-substr()- to
extract part of it, but I would imagine that to be rather hazardous. 

Subsequently, you can use -egen, mode()- to get the most frequent ticker
within the newly created "names".

Here is the second part:


input str20(Name Ticker)
"AOL Time Warner" "AOL"
"AOL Time Warner" "TW"
"AOL Time Warner" "TWX"
"AOL Time Warner" "TWX"
"AOL Time Warner" "T"
"Microsoft" "MS" 


//trim the name to get rid of blanks
replace Name=trim(Name)

bys Name: egen freqtick= /* 
 */ mode(Ticker)
list, noobs


I have a very large dataset of companies over time, and I have two
identifiers for these companies - name and ticker. The problem is that
two identifiers are not always consistent. For instance:

Name, Ticker

AOL Time Warner, AOL
AOL Time Warner, TW
AOL Time Warner, TWX
AOL Time Warner Inc, TWX
AOL Time Warner Inc, T
Microsoft, MS

Basically the first 5 observations provide data about the same entity,
Time Warner, and I need a way of recognizing that these are all the same
company. What I think will work is to check those names for which
tickers exist, and use the ticker which appears in the dataset the most,
put this most frequent ticker in a new variable New_Ticker. Here is how
data should now look: 

Name, Ticker, New_Ticker

AOL Time Warner, AOL, TWX
AOL Time Warner, TW, TWX
AOL Time Warner, TWX, TWX
AOL Time Warner Inc, TWX, TWX
AOL Time Warner Inc, T, TWX
Microsoft, MS, MS

I am unable to figure out how to create this new variable New_ticker,
basically has the most frequently used ticker in cases where the same
has multiple tickers. I will be very grateful for any help on how to
a variable which does the above.

*   For searches and help try:

© Copyright 1996–2017 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index