************************************************************************************************************************************************************; * This code produces wealth creation outcomes as of December 31, 2019 for all firms that issued common stock contained in CRSP; * If all references to 2019 are replaced with 2016 this file also reproduces outcomes contained in the JFE paper; ************************************************************************************************************************************************************; libname in 'C:\KEY FILES\SAS\data'; *the folder containing CRSP and Fama French data; /*************************************************************/ /* 1) Read base data sets /*************************************************************/ * CRSP monthly data, obtained by a web query of the CRSP monthly stock file, through Wharton WRDS; data crsp; set in.CRSP_upto2019; if shrcd in (10,11,12); *consider only common stocks; year=year(date); month=month(date); run; * risk-free rate from July 1926 to December 2019; * available at Kenneth R. French's website(http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html); * before reading here, the raw dataset has been modified to add the date variable name and to save only the requisite rows; proc import out=rf0 datafile="C:\KEY FILES\SAS\data\FamaFrenchData_2019.CSV" dbms=csv replace; datarow=2; getnames=yes; guessingrows=10000; run; data rf; set rf0; rf=rf/100; *change units; year=int(date/100); *note that date variable has a numeric format yyyymm; month=mod(date,100); *create year and month variables to merge the risk-free rate information with the CRSP dataset; keep year month rf; run; proc means n mean min max data = rf; title 'interest rate data'; /*************************************************************/ /* 2) Construct sample /*************************************************************/ proc sort data=crsp; by date; proc sort data=rf; by year month; data smpl0; merge crsp rf; by year month; drop year month; run; * treatment for negative price and to include delisting returns; proc sort data=smpl0; by permno date; data smpl1; set smpl0; by permno date; prc = abs(prc); *have all positive prices; if missing(ret)=1 and missing(dlret)=0 then ret=dlret; *if return is missing, take delisitng return instead if available; else if missing(ret)=0 and missing(dlret)=0 then ret=(1+ret)*(1+dlret)-1; *if both return and delisting return are available, take into account both; else ret=ret; run; * treatment for missing price, so that market cap can be computed; proc sort data=smpl1; by permno date; data smpl2; set smpl1; by permno date; retain prc2 0; if first.permno=0 & missing(prc)=0 then prc2=prc; if first.permno=0 & missing(prc)=1 & missing(ret)=0 then prc2=prc2*(1+ret); *if price information is missing, define price as previous price multiplied by return; if first.permno=0 & missing(prc)=1 & missing(ret)=1 then prc2=prc2; *if both price and return information are missng, use previous price; if first.permno=1 then prc2=prc; if missing(prc)=1 then prc=prc2; exret=ret-rf; *define excess return; run; * create lagged market cap; proc sort data=smpl2; by permno date; run; data smpl3; set smpl2; by permno date; lprc=lag(prc); lshrout=lag(shrout); *lag price and lag share outstanding; if first.permno then lprc=.; *first month of each firm should not have lag price and lag share outstanding; if first.permno then lshrout=.; mktcap=lprc*lshrout/1000; *change units; run; * delete the few firms with missing share outstanding data on all dates; proc sql; create table smpl4 (where=(sumshr>0)) as select *, sum(shrout) as sumshr from smpl3 as a group by permno; quit; * final treatments; proc sort data=smpl4; by permno date; data smpl5; set smpl4; by permno; if '31Jul1926'd<=date<='31Dec2019'd; *sample extends from July 1926 to December 2019; if missing(exret)=0; *each observation should have excess return to calculate dollar gain; drop sumshr; run; /*************************************************************/ /* 3) Create a file with first date, last date and firm name /*************************************************************/ * first date for security; data temp; set smpl5; keep date permno; run; proc sort data=temp; by permno date; run; data temp; set temp; by permno date; if first.permno; firstdate=date; drop date; run; * last date for security; data temp2; set smpl5; keep date permno permco comnam ret; run; proc sort data=temp2; by permno descending date; data temp2; set temp2; by permno descending date; if first.permno; lastdate=date; drop date; run; * have both first date and last date for security; data names; merge temp temp2; by permno; life=12*year(lastdate)+month(lastdate)-12*year(firstdate)-month(firstdate)+1; *life of a security; format firstdate date7.; *change data format as dd-mmm-yy; format lastdate date7.; *change data format as dd-mmm-yy; run; * first date for firm; proc sort data=names; by permco firstdate; run; data first; set names; by permco firstdate; if first.permco; run; * last date for firm; proc sort data=names; by permco descending lastdate; run; data last; set names; by permco descending lastdate; if first.permco; run; * have both first date and last date for firm; proc sql; create table firstlast as select a.permco, a.firstdate, b.lastdate from first as a, last as b where a.permco=b.permco; quit; * select final name associated with a permco -- if more than one security on last date of firm, take one with longest life; data temp3; set names; run; *26257; proc sort data=temp3; by permco descending lastdate descending life; run; data temp3; set temp3; l1permco=lag1(permco); if permco=l1permco then delete; drop l1permco; run; proc sql; create table firminfo as select a.*, b.comnam from firstlast as a, temp3 as b where a.permco=b.permco; quit; /*************************************************************/ /* 4) Calculate lifetime wealth /*************************************************************/ * create dollar gain at security level as excess return mulitplied by lagged market cap; data gain0; set smpl5; dollargain = exret*mktcap; run; * * get firm dollar gain -- sum of dollar gain across permno for common permco; proc sort data=gain0; by date permno permco; proc means noprint data=gain0; var dollargain; by date permno permco; output out=firmgain0 (drop=_freq_ _type_) sum=firmgain; run; * get market dollar gain -- sum of dollar gain in each month; proc sort data=gain0; by date; proc means noprint data=gain0; var dollargain rf; by date; output out=mktgain0 (drop=_freq_ _type_ meangain sumrf) sum=mktgain sumrf mean=meangain rf; run; * create future value (fv) factors from given month to end of sample by linking subsequent one month treasury returns'; data fv0; set mktgain0; ccrf=log(1+rf); *log risk-free late; keep date ccrf; run; proc sort data=fv0; by descending date; run; data fv; set fv0; retain sumccrf 0; sumccrf=sumccrf+ccrf; lagsumccrf=lag(sumccrf); fv=exp(lagsumccrf); if year(date) = 2019 and month(date) = 12 then fv = 1; keep date fv; run; proc sort data=fv; by date; run; * get lifetime wealth creation of market -- sum of market dollar gain over time; data mktgain1; merge mktgain0 fv; by date; fvmktgain=fv*mktgain; *calculate future value of market dollar gain; run; proc means noprint data=mktgain1; var fvmktgain; output out=mktlifegain (drop=_freq_ _type_) sum=fvmktdollargain; run; data mktlifegain; set mktlifegain; constant=1; *for merging purpose; run; * get lifetime wealth creation of firm -- sum of firm dollar gain over time; data firmgain1; merge firmgain0 fv; by date; fvfirmgain=fv*firmgain; *calculate future value of firm dollar gain; run; proc sort data=firmgain1; by permco permno; proc means noprint data=firmgain1; var fvfirmgain; by permco; output out=firmlifegain2 sum=fvfirmdollargain; run; data firmlifegain2; set firmlifegain2; constant = 1; *for merging purpose; run; * get percentage of lifetime wealth creation in market; data firmlifegain3; merge firmlifegain2 mktlifegain; by constant; firmlifepct=fvfirmdollargain/fvmktdollargain; if fvfirmdollargain=. then delete; keep fvfirmdollargain fvmktdollargain permco firmlifepct; run; * get cumulative percentage of lifetime wealth creation in market; proc sort data=firmlifegain3; by descending firmlifepct; data firmlifegain4; set firmlifegain3; retain cumpct 0; cumpct = cumpct + firmlifepct; run; /*************************************************************/ /* 5) Finalize the table and export it /*************************************************************/ * add firm information to the wealth creation table; proc sql; create table fintable as select a.permco, a.comnam, b.fvfirmdollargain, b.firmlifepct, b.cumpct, a.firstdate, a.lastdate from firminfo as a right join firmlifegain4 as b on a.permco=b.permco; quit; * see the results for top 50 firms; proc sort data=fintable; by decending fvfirmdollargain; run; data top50; set fintable; if _n_ le 50; run; proc print data=top50; title 'top 50 firms value created, measured as of Dec 2019'; run; * export the table in csv file; proc export data=fintable outfile="C:\KEY FILES\SAS\ReturnDistribution\WealthCreation\WealthCreation_2019.xlsx" dbms=xlsx replace; run;