-------------------------------------------------------------------------------
log: /Volumes/ecxs2/users/baum/compustatcompann/UDCL7A03-2-9210.smcl
log type: smcl
opened on: 17 Feb 2009, 15:30:46
. * organize IRRC gov dataset for merge into UDCL data
. * try merge with both cusip and ticker
. * cfb 7A03 rev 9216 to pick up IRRC for 2006 and Compustat for 2007
. use ../irrcgov/gset,clear
. * create values for missing years by filling prior year
. expand 3 if year==1990 | year==1995
(5926 observations created)
. bysort cn6 year: replace year=year+1 if _n==2
(2963 real changes made)
. bysort cn6 year: replace year=year+2 if _n==2
(2963 real changes made)
. expand 2 if inlist(year,1993,1998,2000,2002,2004,2006)
(11035 observations created)
. bysort cn6 year: replace year=year+1 if inlist(year,1993,1998,2000,2002,2004,
> 2006) & _n==2
(11035 real changes made)
. rename cn6 cnum
. rename ticker smbl
. rename year yeara
. rename coname company
. sort cnum yeara
. save UDCL_IRRC_9210,replace
file UDCL_IRRC_9210.dta saved
.
. * merge compustat with IRRC on cusip as first priority
. use UDCL7A03-9216, clear
. sort cnum yeara
. merge cnum yeara using UDCL_IRRC_9210
variables cnum yeara do not uniquely identify observations in the master data
. tab _merge
_merge | Freq. Percent Cum.
------------+-----------------------------------
1 | 297,227 90.57 90.57
2 | 4,036 1.23 91.80
3 | 26,927 8.20 100.00
------------+-----------------------------------
Total | 328,190 100.00
. save testmerge1,replace
file testmerge1.dta saved
.
. * now check to see if there are firms who have changed cusip but not ticker
. use UDCL_IRRC_9210,clear
. sort smbl yeara
. save UDCL_IRRC_9210,replace
file UDCL_IRRC_9210.dta saved
.
. use testmerge1, clear
. rename _merge mergecusip
. sort smbl yeara
. * merge compustat + IRRC(cusip) with IRRC(smbl)
. merge smbl yeara using UDCL_IRRC_9210
variables smbl yeara do not uniquely identify observations in the master data
. rename _merge mergeticker
. tab mergeticker mergecusip
mergeticke | mergecusip
r | 1 2 3 | Total
-----------+---------------------------------+----------
1 | 296,040 0 7,033 | 303,073
3 | 1,187 4,036 19,894 | 25,117
-----------+---------------------------------+----------
Total | 297,227 4,036 26,927 | 328,190
. g matched = (mergeticker==3 | mergecusip==3)
. tab matched
matched | Freq. Percent Cum.
------------+-----------------------------------
0 | 302,632 90.40 90.40
1 | 32,150 9.60 100.00
------------+-----------------------------------
Total | 334,782 100.00
. keep if matched
(302632 observations deleted)
. duplicates list cnum yeara
Duplicates in terms of cnum yeara
+---------------------------------+
| group: obs: cnum yeara |
|---------------------------------|
| 1 1231 023608 2007 |
| 1 1236 023608 2007 |
| 2 1275 025537 2007 |
| 2 1283 025537 2007 |
| 3 4462 092113 2007 |
|---------------------------------|
| 3 4469 092113 2007 |
| 4 24139 464288 2007 |
| 4 31454 464288 2007 |
| 5 27065 748356 2007 |
| 5 27070 748356 2007 |
+---------------------------------+
. duplicates drop smbl yeara, force
Duplicates in terms of smbl yeara
(750 observations deleted)
. keep if data1<. & fairprice<.
(4566 observations deleted)
. saveold UDCL_IRRC_matched_9210, replace
file UDCL_IRRC_matched_9210.dta saved
. desc
Contains data from testmerge1.dta
obs: 26,834
vars: 120 17 Feb 2009 15:30
size: 12,477,810 (97.6% of memory free)
-------------------------------------------------------------------------------
storage display value
variable name type format label variable label
-------------------------------------------------------------------------------
dnum int %8.0g industry classification code
cnum str6 %6s cusip issuer code
cic str3 %3s cusip issue number and check
digit
smbl str8 %9s stock ticker symbol
spdrc str2 %2s s&p lt domest. issuer credit
rating- cur
spdrcf str1 %1s ftnt- s&p lt dom issuer cred
rating- cur
subdbt str1 %1s s&p subordinated debt rating -
current
spcprc str3 %3s s&p st domest. issuer credit
rating- cur
date int %dD_m_Y fiscal year
data1 double %10.0g cash and short-term investments
(mm$)
data2 double %10.0g receivables - total (mm$)
data5 double %10.0g current liabilities - total (mm$)
data6 double %10.0g assets - total (mm$)
data9 double %10.0g long-term debt - total (mm$)
data10 double %10.0g preferred stock - liquidating
value(mm$)
data12 double %10.0g sales (net) (mm$)
data13 float %9.0g operating income before deprec.
(mm$)
data14 float %9.0g depreciation and amortization
(mm$)
data15 float %9.0g interest expense (mm$)
data16 float %9.0g income taxes - total (mm$)
data17 float %9.0g special items (mm$)
data18 float %9.0g income before extraordinary items
(mm$)
data21 float %9.0g dividends - common (mm$)
data25 float %9.0g common shares outstanding (mm)
data34 double %10.0g debt in current liabilities (mm$)
data35 float %9.0g deferred tax & invest tax
credit...(mm$)
data46 float %9.0g research and development expense
(mm$)
data50 float %9.0g deferred taxes (income account)
(mm$)
data51 float %9.0g investment tax credit (income
acct)(mm$)
data54 float %9.0g common shares used to calculate
eps (mm)
data56 double %10.0g preferred stock - redemption
value (mm$)
data79 float %9.0g debt - convertible (mm$)
data108 float %9.0g sale of common and pref. stock...
(mm$)
data111 double %10.0g long-term debt - issuance...
(mm$)
data114 double %10.0g long-term debt - reduction...
(mm$)
data115 float %9.0g purchase of common and pref.
stock (mm$)
data128 float %9.0g capital expenditures (scf) (mm$)
data129 double %10.0g acquisitions (statement of cf)
(mm$)
data130 float %9.0g preferred stock - carrying value
(mm$)
data179 float %9.0g working capital (balance sheet)
(mm$)
data180 float %9.0g working capital change -
total(soc)(mm$)
data192 float %9.0g extraordinary items (mm$)
data199 double %10.0g price -- fiscal year -- close
($&c)
data236 float %9.0g working capital change- other...
(mm$)
data274 float %9.0g cash & cash equiv-
increase(dec)...(mm$)
data280 byte %8.0g s&p lt domestic issuer credit
rating
data283 int %8.0g s&p st dom. issuer credit rating-
hist
data301 double %10.0g changes in current debt (scf)
(mm$)
coname str28 %28s company name
yeara float %8.0g fiscal year
gvkey str6 %6s Global Company Key
datadate str8 %8s Data Date
indfmt str12 %12s Industry Format
consol str2 %2s Level of Consolidation - Compan
popsrc str2 %2s Population Source
datafmt str12 %12s Data Format
cusip str10 %10s CUSIP
curcd str4 %4s ISO Currency Code
costat str2 %2s Active/Inactive Status Marker
sic str4 %4s Standard Industry Classificatio
company str48 %48s company name
blankcheck byte %8.0g blank check preferred
cboard byte %8.0g classified board
fairprice byte %8.0g fair price
supermajor byte %8.0g supermajority to approve merger
antigreen byte %8.0g antigreenmail
lwcnst byte %8.0g limit ability to act by written
consent
lspmt byte %8.0g limit ability to call special
meeting
dutiesnf byte %8.0g director's duties--nonfinancial
impact
dualclass byte %8.0g dual class common stock
uneqvote byte %8.0g unequal voting rights
lachtr byte %8.0g limit ability to amend charter
ppill byte %8.0g poison pill
advnr byte %8.0g advance notice requirements
confvote byte %8.0g confidential voting
exsvchg byte %8.0g change in control provisions
sparachute byte %8.0g silver parachute
pparachute byte %8.0g pension parachute
cumvote byte %8.0g cumulative voting
labylw byte %8.0g limit ability to amend bylaws
compplan byte %8.0g compensation plan with change in
control
cumvotess byte %8.0g substantial shareholder
gparachute byte %8.0g golden parachutes
oo_buscomp byte %8.0g opt out of buscomb/freezeout law
oo_cashout_pa byte %8.0g opt out of control share cashout
law (pa)
oo_csa byte %8.0g opt out of control share
acquisition law(csa)
oo_duties byte %8.0g opt out of directors duties law
oo_fairprice byte %8.0g opt out of fair price law
oo_rprofits byte %8.0g opt out of recapture of profits
law
oi_bc_ga byte %8.0g opt in to buscomb/freezeout
law(ga)
oi_fp_ga byte %8.0g opt in to fair price law(ga)
oi_csa byte %8.0g opt in to control share
acquisition law(csa)
exch str6 %6s stocke exchange
dirind byte %8.0g director indemnification
dirindc byte %8.0g indemnification contracts
dirliab byte %8.0g director liability
execsv byte %8.0g executive severance agreement
state str2 %2s state of incorporation
de_inc byte %8.0g delaware incorporation
sl_buscomp byte %8.0g business combination law
sl_fairprice byte %8.0g fair price law
sl_csa byte %8.0g control share acquisition law
sl_rprofits byte %8.0g recapture of profits law
sl_cashout byte %8.0g cash out law
sl_duties byte %8.0g director's duties law
date1 long %dD_m_Y beginning day. date used to
create range of time when irrc
data is applicable.
date2 long %dD_m_Y ending day. date used to create
range of time when irrc data is
applicable.
gindex byte %8.0g governance index (gompers, ishii,
metrick)
secretballot byte %8.0g combined secret ballot
goldenparachute byte %8.0g combined golden parachutes
severance byte %8.0g combined severance
page int %8.0g irrc page number
yr_reinc byte %8.0g year of reincorporation
ecumvote byte %8.0g eliminate cumulative voting
empc byte %8.0g employee contracts
scrtbt byte %8.0g secret ballot
cik str10 %10s cik
mergecusip byte %8.0g
mergeticker byte %8.0g
matched float %9.0g
-------------------------------------------------------------------------------
Sorted by:
Note: dataset has changed since last saved
. summ
Variable | Obs Mean Std. Dev. Min Max
-------------+--------------------------------------------------------
dnum | 26834 4569.11 1798.005 100 9997
cnum | 0
cic | 0
smbl | 0
spdrc | 0
-------------+--------------------------------------------------------
spdrcf | 0
subdbt | 0
spcprc | 0
date | 25286 14086.74 1782.753 10958 16802
data1 | 26834 1064.526 8116.97 -.156 292147
-------------+--------------------------------------------------------
data2 | 25096 3432.299 25037.99 0 917361
data5 | 21249 1087.694 5680.49 0 329795
data6 | 26834 10946.84 57303.38 1.144 2187631
data9 | 25229 1914.26 12718.11 0 486876
data10 | 25236 48.68268 250.8895 0 9108
-------------+--------------------------------------------------------
data12 | 26812 4358.782 13144.48 -4234.472 375376
data13 | 24694 781.1813 2809.041 -5743 89492
data14 | 25657 208.5862 740.6552 -7.909 21577
data15 | 22473 187.302 1412.588 0 56943
data16 | 25265 134.2881 537.1998 -5878 27902
-------------+--------------------------------------------------------
data17 | 23938 -48.32435 589.1953 -51066.2 15006
data18 | 26816 250.3468 1257.016 -56121.9 40610
data21 | 25173 95.12195 449.2572 0 36968
data25 | 25214 147.0762 449.4925 0 10862
data34 | 25233 1597.614 15668.58 -882 499293
-------------+--------------------------------------------------------
data35 | 23594 259.7642 1091.993 -71.665 36713
data46 | 12496 162.5663 598.8358 0 12183
data50 | 22601 6.415079 171.3281 -6570 5268
data51 | 14556 1.90365 25.05518 -.899 2400
data54 | 25158 146.7484 448.7862 .001 10839
-------------+--------------------------------------------------------
data56 | 25242 48.96394 253.4667 -.52 9108
data79 | 23122 48.66904 211.5346 0 8050
data108 | 22836 50.84223 213.7624 -3.143 10413
data111 | 22649 700.8933 6431.94 -.204 347108
data114 | 22914 525.793 4911.944 0 280864
-------------+--------------------------------------------------------
data115 | 22161 113.2876 580.1901 -.905 29558
data128 | 24802 279.9598 1052.643 -28.61 33143
data129 | 22142 92.97105 579.1773 -4971 21570
data130 | 25270 37.16364 207.5724 -79 9108
data179 | 21112 318.5705 1314.215 -33780 55597
-------------+--------------------------------------------------------
data180 | 0
data192 | 24926 -12.83208 429.8927 -54235 2534
data199 | 25190 66.82162 1594.816 .001 109990
data236 | 0
data274 | 23557 31.44609 527.1468 -24785 23209
-------------+--------------------------------------------------------
data280 | 14115 10.90776 3.689734 2 29
data283 | 6070 102.4662 1.284313 101 111
data301 | 13068 32.17695 2403.978 -174455 98688
coname | 0
yeara | 26834 1999.053 5.129813 1990 2007
-------------+--------------------------------------------------------
gvkey | 0
datadate | 0
indfmt | 0
consol | 0
popsrc | 0
-------------+--------------------------------------------------------
datafmt | 0
cusip | 0
curcd | 0
costat | 0
sic | 0
-------------+--------------------------------------------------------
company | 0
blankcheck | 26834 .8612581 .3456834 0 1
cboard | 26834 .5823955 .4931734 0 1
fairprice | 26834 .2668257 .4423087 0 1
supermajor | 26834 .163673 .3699854 0 1
-------------+--------------------------------------------------------
antigreen | 26834 .0475889 .2128987 0 1
lwcnst | 26834 .3556309 .478713 0 1
lspmt | 26834 .3728479 .4835711 0 1
dutiesnf | 26834 .074905 .2632429 0 1
dualclass | 26834 .0968175 .2957146 0 1
-------------+--------------------------------------------------------
uneqvote | 26834 .0174778 .1310458 0 1
lachtr | 26834 .0278751 .164618 0 1
ppill | 26834 .5338004 .4988655 0 1
advnr | 26834 .5143847 .4998023 0 1
confvote | 26834 .0918611 .2888351 0 1
-------------+--------------------------------------------------------
exsvchg | 26834 .5391295 .4984758 0 1
sparachute | 26834 .0248938 .1558043 0 1
pparachute | 26834 .0231423 .1503581 0 1
cumvote | 26834 .1234255 .3289311 0 1
labylw | 26834 .1864798 .3895007 0 1
-------------+--------------------------------------------------------
compplan | 26834 .656965 .4747319 0 1
cumvotess | 26834 .0026459 .0513711 0 1
gparachute | 26834 .0702094 .2555044 0 1
oo_buscomp | 26834 .0262726 .1599479 0 1
oo_cashout~a | 26834 .0031304 .055863 0 1
-------------+--------------------------------------------------------
oo_csa | 26834 .0464709 .2105065 0 1
oo_duties | 26834 .0059253 .0767491 0 1
oo_fairprice | 26834 .0045465 .0672753 0 1
oo_rprofits | 26834 .0171424 .1298045 0 1
oi_bc_ga | 26834 .002944 .05418 0 1
-------------+--------------------------------------------------------
oi_fp_ga | 26834 .0029068 .053837 0 1
oi_csa | 26834 .001118 .0334182 0 1
exch | 0
dirind | 26834 .2805023 .449253 0 1
dirindc | 26834 .1076992 .3100059 0 1
-------------+--------------------------------------------------------
dirliab | 26834 .4884475 .4998758 0 1
execsv | 26834 .0670045 .2500345 0 1
state | 0
de_inc | 26834 .5439741 .4980718 0 1
sl_buscomp | 26834 .8963628 .304795 0 1
-------------+--------------------------------------------------------
sl_fairprice | 26834 .3359171 .4723189 0 1
sl_csa | 26834 .2807632 .4493804 0 1
sl_rprofits | 26834 .1527167 .3597209 0 1
sl_cashout | 26834 .034024 .1812942 0 1
sl_duties | 26834 .0453156 .2079994 0 1
-------------+--------------------------------------------------------
date1 | 26834 14125.08 1830.339 11201 16802
date2 | 23672 14576.77 1551.54 12234 16801
gindex | 26834 9.044049 2.742002 1 19
secretballot | 26834 .0956622 .2941329 0 1
goldenpara~e | 26834 .6093389 .4879077 0 1
-------------+--------------------------------------------------------
severance | 26834 .0863457 .2808791 0 1
page | 20076 851.994 504.6152 3 1923
yr_reinc | 23672 6.48551 22.9452 0 99
ecumvote | 23672 .0675482 .2509742 0 1
empc | 23672 .0219246 .1464406 0 1
-------------+--------------------------------------------------------
scrtbt | 23672 .0043089 .0655019 0 1
cik | 0
mergecusip | 26834 3 0 3 3
mergeticker | 26834 2.476932 .8789564 1 3
matched | 26834 1 0 1 1
. tab year
fiscal year | Freq. Percent Cum.
------------+-----------------------------------
1990 | 1,295 4.83 4.83
1991 | 1,262 4.70 9.53
1992 | 1,241 4.62 14.15
1993 | 1,346 5.02 19.17
1994 | 1,312 4.89 24.06
1995 | 1,365 5.09 29.15
1996 | 1,320 4.92 34.06
1997 | 1,236 4.61 38.67
1998 | 1,677 6.25 44.92
1999 | 1,527 5.69 50.61
2000 | 1,548 5.77 56.38
2001 | 1,457 5.43 61.81
2002 | 1,771 6.60 68.41
2003 | 1,719 6.41 74.82
2004 | 1,847 6.88 81.70
2005 | 1,749 6.52 88.22
2006 | 1,614 6.01 94.23
2007 | 1,548 5.77 100.00
------------+-----------------------------------
Total | 26,834 100.00
.
. log close
log: /Volumes/ecxs2/users/baum/compustatcompann/UDCL7A03-2-9210.smcl
log type: smcl
closed on: 17 Feb 2009, 15:30:58
-------------------------------------------------------------------------------