
. 
. 
. ********************************************************************************
. * Section 1: CEO Turnover
. ********************************************************************************
. 
. use "Raw/ExecuComp_1995_2021_raw.dta", clear

. 
. rename CUSIP EXEC_FULLNAME EXECRANK CO_PER_ROL TITLEANN CEOANN SHROWN_TOT AGE COMMENT GVKEY EXECID YEAR BECAMECEO JOINED_CO REJOIN LEFTOFC LEFTCO RELEFT PCEO PCFO TITLE REASON EXEC_LNAME EXEC_FNAME EXEC_MNAME GENDER NAMEPREFIX PAGE, lower

. destring gvkey, replace
gvkey: all characters numeric; replaced as long

. 
. keep if ceoann == "CEO"
(251,579 observations deleted)

. 
. gsort gvkey year

. duplicates drop gvkey year, force

Duplicates in terms of gvkey year

(43 observations deleted)

. 
. gen ceo_last_year = exec_fullname[_n-1] if year[_n-1]==year-1
(4,126 missing values generated)

. 
. gen new_ceo=(exec_fullname!=ceo_last_year)

. replace new_ceo=. if missing(ceo_last_year)
(4,126 real changes made, 4,126 to missing)

. 
. gen became_ceo_joined_comp_diff=(becameceo-joined_co)/365
(32,813 missing values generated)

. gen new_ceo_internal = (joined_co < becameceo) if joined_co != . & becameceo != .
(32,813 missing values generated)

. 
. gen new_ceo_internal_1yr = (joined_co < becameceo) if joined_co != . & becameceo != .
(32,813 missing values generated)

. replace new_ceo_internal_1yr = 0 if became_ceo_joined_comp_diff>=0 & became_ceo_joined_comp_diff<=1
(1,334 real changes made)

. 
. save "Intermediate/new_ceo_firm_year_1995_2021.dta", replace
file Intermediate/new_ceo_firm_year_1995_2021.dta saved

. 
. 
. ********************************************************************************
. * Section 2: CRSP Annual Returns
. ********************************************************************************
. 
. use "Raw/crsp.dta", clear

. 
. gen temp=ln(1+ret)

. gen tempx=ln(1+retx)

. collapse (mean)ret retx (sum)temp tempx, by(permno fyear)

. gen annual_return=exp(temp)-1

. gen annual_returnx=exp(tempx)-1

. drop temp tempx

. su

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
      permno |    160,369    61865.42    32248.57      10001      93436
       fyear |    160,369    2012.428    6.212611       2002       2022
         ret |    160,369    .0075322     .064093  -.9618518   5.215842
        retx |    160,369      .00587    .0641636  -.9618518   5.215842
annual_ret~n |    160,369    .1019126    .7204381  -.9999938   95.80555
-------------+---------------------------------------------------------
annual_ret~x |    160,369    .0818044    .7179884  -.9999938   95.80555

. foreach var in ret retx annual_return annual_returnx{
  2.         replace `var'=`var'*100
  3. }
(160,341 real changes made)
(160,341 real changes made)
(160,325 real changes made)
(160,319 real changes made)

. rename fyear year

. sort permno year

. xtset permno year

Panel variable: permno (unbalanced)
 Time variable: year, 2002 to 2022, but with gaps
         Delta: 1 unit

. foreach var in ret retx annual_return annual_returnx{
  2.         gen f_`var'=f.`var'
  3. }
(19,833 missing values generated)
(19,833 missing values generated)
(19,833 missing values generated)
(19,833 missing values generated)

. save "Intermediate/crsp_annual.dta", replace
file Intermediate/crsp_annual.dta saved

. 
. 
. ********************************************************************************
. * Section 3: SG&A-Based Organizational Capital
. ********************************************************************************
. 
. use "Raw/Compustat All Raw.dta", clear

. keep GVKEY fyear xsga

. duplicates drop

Duplicates in terms of all variables

(2,656 observations deleted)

. duplicates report GVKEY fyear

Duplicates in terms of GVKEY fyear

--------------------------------------
   Copies | Observations       Surplus
----------+---------------------------
        1 |       317076             0
--------------------------------------

. save "Intermediate/sga.dta", replace
file Intermediate/sga.dta saved

. 
. import excel "Raw/CPI.xlsx", sheet("Sheet1") firstrow clear
(16 vars, 110 obs)

. keep Year Avg

. rename Year fyear

. save "Intermediate/cpi.dta", replace
file Intermediate/cpi.dta saved

. 
. use "Intermediate/sga.dta", clear

. merge m:1 fyear using "Intermediate/cpi.dta"

    Result                      Number of obs
    -----------------------------------------
    Not matched                            40
        from master                         2  (_merge==1)
        from using                         38  (_merge==2)

    Matched                           317,074  (_merge==3)
    -----------------------------------------

. keep if _m==3
(40 observations deleted)

. drop _m

. gen real_xsga=xsga/Avg
(72,270 missing values generated)

. destring GVKEY, gen(gvkey)
GVKEY: all characters numeric; gvkey generated as long

. drop GVKEY

. sort gvkey fyear

. bysort gvkey: gen mm=_n

. sort gvkey fyear

. bysort gvkey: gen nn=_N

. 
. sort gvkey fyear

. xtset gvkey fyear

Panel variable: gvkey (unbalanced)
 Time variable: fyear, 1950 to 2021, but with gaps
         Delta: 1 unit

. sort gvkey fyear

. bysort gvkey: gen annual_growth=(real_xsga-l.real_xsga)/l.real_xsga
(96,806 missing values generated)

. bysort gvkey: egen avg_growth=mean(annual_growth)
(54,824 missing values generated)

. winsor2 avg_growth

. 
. su avg_growth_w, detail

                         avg_growth
-------------------------------------------------------------
      Percentiles      Smallest
 1%    -.2104159      -.2104159
 5%    -.0484936      -.2104159
10%    -.0103452      -.2104159       Obs             262,250
25%      .029881      -.2104159       Sum of wgt.     262,250

50%     .0736968                      Mean           .1262424
                        Largest       Std. dev.      .2113312
75%     .1504785       1.381977
90%     .2956756       1.381977       Variance       .0446609
95%     .4692395       1.381977       Skewness       3.418711
99%     1.381977       1.381977       Kurtosis       18.34355

. save, replace
file Intermediate/sga.dta saved

. 
. export delimited using "Intermediate/sga.csv", replace
file Intermediate/sga.csv saved

. 
. * ── PAUSE: Run code/OC.py before continuing ──────────────────────────────────
. * OC.py reads Intermediate/sga.csv and produces Intermediate/OC.csv
. 
. import delimited using "Intermediate/OC.csv", clear
(encoding automatically selected: ISO-8859-1)
(12 vars, 317,074 obs)

. drop v1

. winsor2 oc

. save "Intermediate/OC.dta", replace
file Intermediate/OC.dta saved

. 
. 
. ************** Merge with Compustat **************
. 
. use "Raw/Compustat All Raw.dta", clear

. destring GVKEY, gen(gvkey)
GVKEY: all characters numeric; gvkey generated as long

. drop if missing(fyear)
(2 observations deleted)

. gen lnat=ln(at)
(21,125 missing values generated)

. drop if missing(lnat)
(21,125 observations deleted)

. keep gvkey fyear sic naics lnat at sale ni mkvalt dltt dlc prcc_c prcc_f csho ceq tic cik cusip conm conml addzip

. duplicates drop

Duplicates in terms of all variables

(2,650 observations deleted)

. duplicates report gvkey fyear

Duplicates in terms of gvkey fyear

--------------------------------------
   Copies | Observations       Surplus
----------+---------------------------
        1 |       295955             0
--------------------------------------

. sort gvkey fyear

. xtset gvkey fyear

Panel variable: gvkey (unbalanced)
 Time variable: fyear, 1950 to 2021, but with gaps
         Delta: 1 unit

. bysort gvkey: gen sale_growth=(sale-l.sale)/l.sale
(31,740 missing values generated)

. save "Intermediate/compustat_subset.dta", replace
file Intermediate/compustat_subset.dta saved

. 
. use "Intermediate/compustat_subset.dta", clear

. merge 1:1 gvkey fyear using "Intermediate/OC.dta"

    Result                      Number of obs
    -----------------------------------------
    Not matched                        21,119
        from master                         0  (_merge==1)
        from using                     21,119  (_merge==2)

    Matched                           295,955  (_merge==3)
    -----------------------------------------

. drop if _m==2
(21,119 observations deleted)

. drop _m

. tab mm

         mm |      Freq.     Percent        Cum.
------------+-----------------------------------
          1 |     24,676        8.34        8.34
          2 |     22,822        7.71       16.05
          3 |     20,590        6.96       23.01
          4 |     18,594        6.28       29.29
          5 |     16,802        5.68       34.97
          6 |     15,170        5.13       40.09
          7 |     13,843        4.68       44.77
          8 |     12,725        4.30       49.07
          9 |     11,644        3.93       53.00
         10 |     10,629        3.59       56.59
         11 |      9,734        3.29       59.88
         12 |      8,981        3.03       62.92
         13 |      8,243        2.79       65.70
         14 |      7,614        2.57       68.28
         15 |      7,087        2.39       70.67
         16 |      6,532        2.21       72.88
         17 |      6,018        2.03       74.91
         18 |      5,588        1.89       76.80
         19 |      5,174        1.75       78.55
         20 |      4,839        1.64       80.18
         21 |      4,526        1.53       81.71
         22 |      4,237        1.43       83.14
         23 |      3,920        1.32       84.47
         24 |      3,627        1.23       85.69
         25 |      3,347        1.13       86.82
         26 |      3,065        1.04       87.86
         27 |      2,788        0.94       88.80
         28 |      2,565        0.87       89.67
         29 |      2,363        0.80       90.47
         30 |      2,163        0.73       91.20
         31 |      1,987        0.67       91.87
         32 |      1,851        0.63       92.50
         33 |      1,713        0.58       93.07
         34 |      1,613        0.55       93.62
         35 |      1,506        0.51       94.13
         36 |      1,377        0.47       94.59
         37 |      1,240        0.42       95.01
         38 |      1,140        0.39       95.40
         39 |      1,056        0.36       95.75
         40 |        984        0.33       96.09
         41 |        924        0.31       96.40
         42 |        869        0.29       96.69
         43 |        829        0.28       96.97
         44 |        783        0.26       97.24
         45 |        749        0.25       97.49
         46 |        712        0.24       97.73
         47 |        687        0.23       97.96
         48 |        661        0.22       98.19
         49 |        606        0.20       98.39
         50 |        512        0.17       98.56
         51 |        396        0.13       98.70
         52 |        364        0.12       98.82
         53 |        339        0.11       98.94
         54 |        313        0.11       99.04
         55 |        294        0.10       99.14
         56 |        273        0.09       99.23
         57 |        246        0.08       99.32
         58 |        233        0.08       99.39
         59 |        217        0.07       99.47
         60 |        199        0.07       99.54
         61 |        147        0.05       99.58
         62 |        142        0.05       99.63
         63 |        134        0.05       99.68
         64 |        130        0.04       99.72
         65 |        126        0.04       99.76
         66 |        117        0.04       99.80
         67 |        113        0.04       99.84
         68 |        108        0.04       99.88
         69 |        102        0.03       99.91
         70 |         99        0.03       99.95
         71 |         92        0.03       99.98
         72 |         66        0.02      100.00
------------+-----------------------------------
      Total |    295,955      100.00

. label var mm "Number of years in dataset"

. save "Intermediate/OC_compustat.dta", replace
file Intermediate/OC_compustat.dta saved

. 
. 
. quietly log close
