************************************************************************************************************************************ * SAS codes used in * * Using Accounting Earnings and Aggregate Supply and Demand Indicators to Estimate Firm-level Systematic Risk * * * ************************************************************************************************************************************ ***** annual aggregate variables ; proc sort data=cfbeta.Proxies; by F1; run; data TFP1; set cfbeta.Proxies; TFP=TFP1/100; *TFP1 in percentage from Fernald (2012); year=put(F1,4.); run; proc arima data=TFP1 ; * reprot serial correlation; identify var=TFP nlag=24; run; proc sort data=cfbeta.Proxies2; by F1; run; data wealth1; set cfbeta.Proxies2; wealth=wealth2; *wealth2 = Fed household total net worth Z1/Z1/FL152090005 minus corporate equities (Z1/Z1/FL153064105) and indirectly held corporate equities (Z1/Z1/FL153064175); wealth_D1=wealth2-lag1(wealth2); * take the first difference; year=put(F1,4.); run; proc arima data=wealth1 ; * reprot serial correlation; identify var=wealth_D1 nlag=24; run; proc sql ; create table wealth4 as select distinct b.*, a.TFP from work.wealth1 as b left join work.TFP1 as a on (b.year=a.year ); quit; * value-weighted aggregate stock returns based on U.S. common equities from CRSP; proc sort data=cfbeta.Crsp_monthly_1925_2016; by permno date; run; data CRSP1; set cfbeta.Crsp_monthly_1925_2016; year=year(date); month=month(date); lyear=put(year,4.); drop year; rename lyear=year; lpermno=put(permno,5.); drop permno; rename lpermno=permno; SHRCDI=0; if SHRCD=10 then SHRCDI=1; if SHRCD=11 then SHRCDI=1; if SHRCD=12 then SHRCDI=1; if SHRCDI~=1 then delete; * U.S. common equities from CRSP; R=RET; if RET=. and DLRET~=. then R=DLRET; if RET=. and DLRET~=. and DLSTCD>519 and DLSTCD<585 then R=-0.3; * performance-based deslisting code 500 520-584; if RET=. and DLRET~=. and DLSTCD=500 then R=-0.3; * performance-based deslisting code 500 520-584; MVE=PRC*SHROUT/1000; if PRC<0 then MVE=-PRC*SHROUT/1000; MVElag=lag1(MVE); if (permno NE lag1(permno)) then MVElag=.; run; proc sort data=CRSP1 ; by date; run; proc means data=CRSP1 noprint; by date; weight MVElag; var R; output out=CRSP2 mean=VWRET; run; data CRSP3; set CRSP2; lret=log(1+VWRET); year=year(date); lyear=put(year,4.); drop year; rename lyear=year; run; proc means data=CRSP3 noprint; by year; var lret; output out=CRSP4 sum=; run; data CRSP5; set CRSP4; RET=exp(lret)-1; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table wealth5 as select distinct b.*, a.RET from work.wealth4 as b left join work.CRSP5 as a on (b.year=a.link_year ); quit; *calculate aggregate earnings growth based on U.S. common equities from CRSP ; data a; set collus.Compustat_annual_1950_2016; BVE1=AT-LT+TXDITC-PSTKL-PSTKRV-PSTK; if PSTKL=. or PSTKRV=. or PSTK=. then BVE1=AT-LT+TXDITC; if PSTKL=. or PSTKRV=. or PSTK=. or TXDITC=. then BVE1=AT-LT; E1=OIADP-PDVC; if PDVC=. and OIADP~=. then E1=OIADP; if PDVC=. and OIADP=. then E1=IB; year=put(fyear,4.); permno=put(lpermno,5.); run; data b2; set CRSP1; if month~=12 then delete; run; proc sql ; create table firm_annually3 as select distinct b.*, a.SHRCDI, a.MVE from work.a as b left join work.b2 as a on (b.permno=a.permno and b.year=a.year) order by b.gvkey, b.year; quit; data firm_annually8; set firm_annually3; E1lag1=lag1(E1); if (gvkey NE lag1(gvkey)) or ((year-1) NE lag1(year)) then E1lag1=.; MVElag2=lag2(MVE); if (gvkey NE lag2(gvkey)) or ((year-2) NE lag2(year)) then MVElag2=.; if SHRCDI~=1 then delete; run; proc sort data=firm_annually8; by year ; run; proc means data=firm_annually8 noprint; by year ; var E1 E1lag1 MVElag2 ; output out=MKT1 sum=; run; data MKT5; set MKT1; AGGE=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table wealth51 as select distinct b.*, a.AGGE from work.wealth5 as b left join work.MKT5 as a on (b.year=a.link_year ); quit; data wealth6; set wealth51; if year<1964 then delete; if year>2011 then delete; run; ***** Table 1; proc means data=wealth6 n mean min p10 p25 p50 p75 p90 max std; var AGGE TFP wealth_D1 ; run; proc corr data=wealth6 pearson spearman outp=p_correlation outs=s_correlation; var AGGE TFP wealth_D1 ; run; ***** aggregate time-series regression (Table 2); ods listing close; proc reg data=wealth6 ADJRSQ ; model AGGE = TFP wealth_D1 /SPEC ACOV ; run; ***** B/M portfolio (Table 3); data BM1; set firm_annually8; BM=BVE1/MVE; if BVE1<0 then delete; run; proc sort data=BM1; by permno year; run; data BM2; set BM1; BMlag1=lag1(BM); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then BMlag1=.; MVElag1=lag1(MVE); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then MVElag1=.; MVElag2=lag2(MVE); if (permno NE lag2(permno)) or ((year-2) NE lag2(year)) then MVElag2=.; BVE1lag1=lag1(BVE1); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then BVE1lag1=.; BVE1lag2=lag2(BVE1); if (permno NE lag2(permno)) or ((year-2) NE lag2(year)) then BVE1lag2=.; ATlag1=lag1(AT); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then ATlag1=.; ATlag2=lag2(AT); if (permno NE lag2(permno)) or ((year-2) NE lag2(year)) then ATlag2=.; run; proc sort data=BM2; by year; run; proc rank data=BM2 groups=5 out=BM3; by year ; var BMlag1 ; ranks BMR ; run; * winsor +/-1%; %macro winsor(dsetin = BM3, dsetout = BM4, byvar = YEAR, vars = E1 E1lag1 BVE1lag1 BVE1lag2 ATlag1 ATlag2 , type=winsor, pctl=1 99); %if &dsetout = %then %let dsetout = &dsetin; %let varL=; %let varH=; %let xn=1; %do %until ( %scan(&vars,&xn)= ); %let token = %scan(&vars,&xn); %let varL = &varL &token.L; %let varH = &varH &token.H; %let xn=%EVAL(&xn + 1); %end; %let xn=%eval(&xn-1); data xtemp; set &dsetin; run; %let dropvar = ; %if &byvar = none %then %do; data xtemp; set xtemp; xbyvar = 1; run; %let byvar = xbyvar; %let dropvar = xbyvar; %end; proc sort data = xtemp; by &byvar; run; proc univariate data = xtemp noprint; by &byvar; var &vars; output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H; run; data &dsetout; merge xtemp xtemp_pctl; by &byvar; array trimvars{&xn} &vars; array trimvarl{&xn} &varL; array trimvarh{&xn} &varH; do xi = 1 to dim(trimvars); %if &type = winsor %then %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi}; if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi}; end; %end; %else %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then delete; if (trimvars{xi} > trimvarh{xi}) then delete; end; %end; end; drop &varL &varH &dropvar xi; run; %mend winsor; run; %winsor; *check outlier; proc means data=BM3 n nmiss min p10 p25 mean median p75 p90 max std; var BM E1 E1lag1 BVE1lag1 MVElag2 ATlag1 ATlag2 ; title 'before winsorize '; run; proc means data=BM4 n nmiss min p10 p25 mean median p75 p90 max std; var BM E1 E1lag1 BVE1lag1 MVElag2 ATlag1 ATlag2 ; title 'aftere winsorize '; run; proc sort data=BM4; by BMR year ; run; proc means data=BM4 noprint; by BMR year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ATlag1 ATlag2 ; output out=BM5 sum=; run; data BM6; set BM5; BME=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table BM7 as select distinct b.*, a.RET, a.AGGE, a.TFP, a.wealth_D1, a.wealth_RD1 from work.BM6 as b left join extl.wealth6 as a on (b.link_year=a.year); quit; proc sort data=BM4; by year ; run; proc means data=BM4 noprint; by year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ; output out=MKT sum=; run; data MKT2; set MKT; MKT=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table BM71 as select distinct b.*, a.MKT, a.MKT2, a.MKT3 from work.BM7 as b left join work.MKT2 as a on (b.year=a.link_year); quit; * portfolio t-s regression; data BM8; set BM71; if year<1964 then delete; if year>2011 then delete; if BMR=. then delete; run; proc sort data=BM8; by BMR; run; ods listing close; proc reg data=BM8 ADJRSQ ; by BMR; model BME = wealth_D1 TFP /SPEC ACOV ; run; ***** Return momentumn (Table 4); proc sort data=CRSP1; by permno date; run; data MMT1; set CRSP1; MMT=lag1(R)+lag2(R)+lag3(R)+lag4(R)+lag5(R)+lag6(R)+lag7(R)+lag8(R)+lag9(R)+lag10(R)+lag11(R)+lag12(R); link_year=year+1; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; data MMT2; set firm_annually8; link_year=year(datadate); llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; month=month(datadate); run; proc sql ; create table MMT3 as select distinct b.*, a.MMT from work.MMT2 as b left join work.MMT1 as a on (b.permno=a.permno and b.link_year=a.link_year and b.month=a.month) order by b.gvkey, b.year; quit; proc sort data=MMT3; by year; run; proc rank data=MMT3 groups=5 out=MMT4; by year ; var MMT ; ranks MMTR ; run; %macro winsor(dsetin = MMT4, dsetout = MMT5, byvar = YEAR, vars = E1 E1lag1 ATlag1 ATlag2 BVE1lag2 BVE1lag1 , type=winsor, pctl=1 99); %if &dsetout = %then %let dsetout = &dsetin; %let varL=; %let varH=; %let xn=1; %do %until ( %scan(&vars,&xn)= ); %let token = %scan(&vars,&xn); %let varL = &varL &token.L; %let varH = &varH &token.H; %let xn=%EVAL(&xn + 1); %end; %let xn=%eval(&xn-1); data xtemp; set &dsetin; run; %let dropvar = ; %if &byvar = none %then %do; data xtemp; set xtemp; xbyvar = 1; run; %let byvar = xbyvar; %let dropvar = xbyvar; %end; proc sort data = xtemp; by &byvar; run; proc univariate data = xtemp noprint; by &byvar; var &vars; output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H; run; data &dsetout; merge xtemp xtemp_pctl; by &byvar; array trimvars{&xn} &vars; array trimvarl{&xn} &varL; array trimvarh{&xn} &varH; do xi = 1 to dim(trimvars); %if &type = winsor %then %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi}; if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi}; end; %end; %else %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then delete; if (trimvars{xi} > trimvarh{xi}) then delete; end; %end; end; drop &varL &varH &dropvar xi; run; %mend winsor; run; %winsor; proc means data=MMT4 n nmiss min p10 p25 mean median p75 p90 max std; var MMT E1 E1lag1; title 'before winsorize '; run; proc means data=MMT5 n nmiss min p10 p25 mean median p75 p90 max std; var MMT E1 E1lag1; title 'aftere winsorize '; run; proc sort data=MMT5; by MMTR year ; run; proc means data=MMT5 noprint; by MMTR year ; var E1 E1lag1 MVElag2 BVE1lag2 BVE1lag1 ATlag1 ATlag2 ; output out=MMT6 sum=; run; data MMT7; set MMT6; MMTE=(E1-E1lag1)/MVElag2; MMTE2=(E1-E1lag1)/ATlag2; MMTE4=(E1-E1lag1)/BVE1lag2; ROEt=E1/BVE1lag1; ROEt1=E1lag1/BVE1lag2; MMTE3=(ROEt-ROEt1)/ROEt1; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table MMT8 as select distinct b.*, a.RET, a.AGGE, a.TFP, a.wealth_D1, a.wealth_RD1 from work.MMT7 as b left join extl.wealth6 as a on (b.link_year=a.year); quit; proc sort data=MMT5; by year ; run; proc means data=MMT5 noprint; by year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ; output out=MKT sum=; run; data MKT2; set MKT; MKT=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table MMT82 as select distinct b.*, a.MKT, a.MKT2, a.MKT3 from work.MMT8 as b left join work.MKT2 as a on (b.year=a.link_year); quit; * portfolio t-s regression; proc sort data=MMT82; by MMTR year ; run; data MMT9; set MMT82; if year<1964 then delete; if year>2011 then delete; if MMTR=. then delete; run; proc sort data=MMT9; by MMTR; run; ods listing close; proc reg data=MMT9 ADJRSQ ; by MMTR; model MMTE = wealth_D1 TFP /SPEC ACOV ; run; ***** firm size (Table 5) ; data size1; set firm_annually8; run; proc sort data=size1; by permno year; run; data size2; set size1; MVElag1=lag1(MVE); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then MVElag1=.; MVElag2=lag2(MVE); if (permno NE lag2(permno)) or ((year-2) NE lag2(year)) then MVElag2=.; BVE1lag1=lag1(BVE1); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then BVE1lag1=.; BVE1lag2=lag2(BVE1); if (permno NE lag2(permno)) or ((year-2) NE lag2(year)) then BVE1lag2=.; run; proc sort data=size2; by year; run; proc rank data=size2 groups=5 out=size3; by year ; var MVElag1 ; ranks sizeR ; run; *deal with outlier winsor or delete; %macro winsor(dsetin = size3, dsetout = size4, byvar = YEAR, vars = E1 E1lag1 ATlag1 ATlag2 BVE1lag2 BVE1lag1 , type=winsor, pctl=1 99); %if &dsetout = %then %let dsetout = &dsetin; %let varL=; %let varH=; %let xn=1; %do %until ( %scan(&vars,&xn)= ); %let token = %scan(&vars,&xn); %let varL = &varL &token.L; %let varH = &varH &token.H; %let xn=%EVAL(&xn + 1); %end; %let xn=%eval(&xn-1); data xtemp; set &dsetin; run; %let dropvar = ; %if &byvar = none %then %do; data xtemp; set xtemp; xbyvar = 1; run; %let byvar = xbyvar; %let dropvar = xbyvar; %end; proc sort data = xtemp; by &byvar; run; proc univariate data = xtemp noprint; by &byvar; var &vars; output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H; run; data &dsetout; merge xtemp xtemp_pctl; by &byvar; array trimvars{&xn} &vars; array trimvarl{&xn} &varL; array trimvarh{&xn} &varH; do xi = 1 to dim(trimvars); %if &type = winsor %then %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi}; if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi}; end; %end; %else %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then delete; if (trimvars{xi} > trimvarh{xi}) then delete; end; %end; end; drop &varL &varH &dropvar xi; run; %mend winsor; run; %winsor; proc means data=size3 n nmiss min p10 p25 mean median p75 p90 max std; var E1 E1lag1 BVE1lag1 MVElag2 ; title 'before winsorize'; run; proc means data=size4 n nmiss min p10 p25 mean median p75 p90 max std; var E1 E1lag1 BVE1lag1 MVElag2 ; title 'aftere winsorize '; run; proc sort data=size4; by sizeR year ; run; proc means data=size4 noprint; by sizeR year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ATlag1 ATlag2 ; output out=size5 sum=; run; data size6; set size5; sizeE=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table size7 as select distinct b.*, a.RET, a.AGGE, a.TFP, a.wealth_D1, a.wealth_RD1 from work.size6 as b left join extl.wealth6 as a on (b.link_year=a.year); quit; proc sort data=size4; by year ; run; proc means data=size4 noprint; by year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ; output out=MKT sum=; run; data MKT2; set MKT; MKT=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table size71 as select distinct b.*, a.MKT, a.MKT2, a.MKT3 from work.size7 as b left join work.MKT2 as a on (b.year=a.link_year); quit; * portfolio t-s regression; data size8; set size71; if year<1964 then delete; if year>2011 then delete; if sizeR=. then delete; run; proc sort data=size8; by sizeR; run; ods listing close; proc reg data=size8 ADJRSQ ; by sizeR; model sizeE = TFP wealth_D1 /SPEC ACOV ; run; ****** asset growth (Table 6); data ATG1; set firm_annually8; month=month(datadate); run; proc sql ; create table ATG2 as select distinct b.*, a.CFACSHR, a.SHROUT from work.ATG1 as b left join work.CRSP1 as a on (b.permno=a.permno and b.year=a.year and b.month=a.month) order by b.permno, b.datadate; quit; proc sort data=ATG2; by permno year; run; data ATG2a; set ATG2; SHROUTlag1=lag1(SHROUT); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then SHROUTlag1=.; CFACSHRlag1=lag1(CFACSHR); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then CFACSHRlag1=.; ATPS=AT/SHROUT; ATPSlag1=ATlag1/(SHROUTlag1*CFACSHRlag1/CFACSHR); ATGA=log(ATPS)-log(ATPSlag1); ATGAlag1=lag1(ATGA); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then ATGAlag1=.; run; proc sort data=ATG2a; by year; run; proc rank data=ATG2a groups=5 out=ATG3; by year ; var ATGAlag1 ; ranks ATGR ; run; %macro winsor(dsetin = ATG3, dsetout = ATG4, byvar = YEAR, vars = E1 E1lag1 BVE1lag1 BVE1lag2 ATlag1 ATlag2 , type=winsor, pctl=1 99); %if &dsetout = %then %let dsetout = &dsetin; %let varL=; %let varH=; %let xn=1; %do %until ( %scan(&vars,&xn)= ); %let token = %scan(&vars,&xn); %let varL = &varL &token.L; %let varH = &varH &token.H; %let xn=%EVAL(&xn + 1); %end; %let xn=%eval(&xn-1); data xtemp; set &dsetin; run; %let dropvar = ; %if &byvar = none %then %do; data xtemp; set xtemp; xbyvar = 1; run; %let byvar = xbyvar; %let dropvar = xbyvar; %end; proc sort data = xtemp; by &byvar; run; proc univariate data = xtemp noprint; by &byvar; var &vars; output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H; run; data &dsetout; merge xtemp xtemp_pctl; by &byvar; array trimvars{&xn} &vars; array trimvarl{&xn} &varL; array trimvarh{&xn} &varH; do xi = 1 to dim(trimvars); %if &type = winsor %then %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi}; if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi}; end; %end; %else %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then delete; if (trimvars{xi} > trimvarh{xi}) then delete; end; %end; end; drop &varL &varH &dropvar xi; run; %mend winsor; run; %winsor; proc means data=ATG3 n nmiss min p10 p25 mean median p75 p90 max std; var E1 E1lag1 BVE1lag1 MVElag2 ; title 'before winsorize '; run; proc means data=ATG4 n nmiss min p10 p25 mean median p75 p90 max std; var E1 E1lag1 BVE1lag1 MVElag2 ; title 'aftere winsorize '; run; proc sort data=ATG4; by ATGR year ; run; proc means data=ATG4 noprint; by ATGR year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ATlag2 ATlag1 ; output out=ATG5 sum=; run; data ATG6; set ATG5; ATGE=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table ATG7 as select distinct b.*, a.RET, a.AGGE, a.TFP, a.wealth_D1, a.wealth_RD1 from work.ATG6 as b left join extl.wealth6 as a on (b.link_year=a.year); quit; proc sort data=ATG4; by year ; run; proc means data=ATG4 noprint; by year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ATlag1 ATlag2 ; output out=MKT sum=; run; data MKT2; set MKT; MKT=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table ATG71 as select distinct b.*, a.MKT, a.MKT2, a.MKT3 from work.ATG7 as b left join work.MKT2 as a on (b.year=a.link_year); quit; data ATG8; set ATG71; if year<1964 then delete; if year>2011 then delete; if ATGR=. then delete; run; proc sort data=ATG8; by ATGR; run; ods listing close; proc reg data=ATG8 ADJRSQ ; by ATGR; model ATGE = wealth_D1 TFP /SPEC ACOV ; run; ****** 81 portfolio estimation and firm-level pricing test (Tables 7-8) ; proc sort data=CRSP1; by permno date; run; data MMT1; set CRSP1; MMT=lag1(R)+lag2(R)+lag3(R)+lag4(R)+lag5(R)+lag6(R)+lag7(R)+lag8(R)+lag9(R)+lag10(R)+lag11(R)+lag12(R); link_year=year+1; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; data portfolio1; set firm_annually8; link_year=year(datadate); llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; month=month(datadate); run; proc sql ; create table portfolio2 as select distinct b.*, a.MMT from work.portfolio1 as b left join work.MMT1 as a on (b.permno=a.permno and b.link_year=a.link_year and b.month=a.month) order by b.gvkey, b.year; quit; proc sql ; create table portfolio3 as select distinct b.*, a.CFACSHR, a.SHROUT from work.portfolio2 as b left join work.CRSP1 as a on (b.permno=a.permno and b.year=a.year and b.month=a.month) order by b.permno, b.year; quit; data portfolio4; set portfolio3; BM=BVE1/MVE; if BVE1<0 then BM=.; BMlag1=lag1(BM); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then BMlag1=.; MVElag1=lag1(MVE); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then MVElag1=.; MVElag2=lag2(MVE); if (permno NE lag2(permno)) or ((year-2) NE lag2(year)) then MVElag2=.; BVE1lag1=lag1(BVE1); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then BVE1lag1=.; BVE1lag2=lag2(BVE1); if (permno NE lag2(permno)) or ((year-2) NE lag2(year)) then BVE1lag2=.; ATlag1=lag1(AT); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then ATlag1=.; SHROUTlag1=lag1(SHROUT); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then SHROUTlag1=.; CFACSHRlag1=lag1(CFACSHR); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then CFACSHRlag1=.; ATPS=AT/SHROUT; ATPSlag1=ATlag1/(SHROUTlag1*CFACSHRlag1/CFACSHR); ATGA=log(ATPS)-log(ATPSlag1); ATGAlag1=lag1(ATGA); if (permno NE lag1(permno)) or ((year-1) NE lag1(year)) then ATGAlag1=.; run; proc sort data=portfolio4; by year; run; proc rank data=portfolio4 groups=3 out=portfolio51; by year ; var BMlag1 ; ranks BMR ; run; proc sort data=portfolio4; by year; run; proc rank data=portfolio4 groups=3 out=portfolio52; by year ; var MMT ; ranks MMTR ; run; proc sort data=portfolio4; by year; run; proc rank data=portfolio4 groups=3 out=portfolio53; by year ; var MVElag1 ; ranks sizeR ; run; proc sort data=portfolio4; by year; run; proc rank data=portfolio4 groups=3 out=portfolio54; by year ; var ATGAlag1 ; ranks ATGR ; run; proc sql ; create table portfolio61 as select distinct b.*, a.MMTR from work.portfolio51 as b left join work.portfolio52 as a on (b.permno=a.permno and b.year=a.year ) order by b.permno, a.year; quit; proc sql ; create table portfolio62 as select distinct b.*, a.sizeR from work.portfolio61 as b left join work.portfolio53 as a on (b.permno=a.permno and b.year=a.year ) order by b.permno, a.year; quit; proc sql ; create table portfolio63 as select distinct b.*, a.ATGR from work.portfolio62 as b left join work.portfolio54 as a on (b.permno=a.permno and b.year=a.year ) order by b.permno, a.year; quit; data portfolio7; set portfolio63; if BMR=. then delete; if MMTR=. then delete; if sizeR=. then delete; if ATGR=. then delete; lBMR=put(BMR,1.); lMMTR=put(MMTR,1.); lsizeR=put(sizeR,1.); lATGR=put(ATGR,1.); pflo=compress(lBMR||lMMTR||lsizeR||lATGR); run; **report attrition in Table 7; proc sort data=portfolio7; by gvkey datadate; run; data att1; set portfolio7; pflolag1=lag1(pflo); if (gvkey NE lag1(gvkey)) or ((year-1) NE lag1(year)) then pflolag1=.; pflolag2=lag2(pflo); if (gvkey NE lag2(gvkey)) or ((year-2) NE lag2(year)) then pflolag2=.; pflolag3=lag3(pflo); if (gvkey NE lag3(gvkey)) or ((year-3) NE lag3(year)) then pflolag3=.; pflolag4=lag4(pflo); if (gvkey NE lag4(gvkey)) or ((year-4) NE lag4(year)) then pflolag4=.; pflolag5=lag5(pflo); if (gvkey NE lag5(gvkey)) or ((year-5) NE lag5(year)) then pflolag5=.; pflolag6=lag6(pflo); if (gvkey NE lag6(gvkey)) or ((year-6) NE lag6(year)) then pflolag6=.; pflolag7=lag7(pflo); if (gvkey NE lag7(gvkey)) or ((year-7) NE lag7(year)) then pflolag7=.; pflolag8=lag8(pflo); if (gvkey NE lag8(gvkey)) or ((year-8) NE lag8(year)) then pflolag8=.; pflolag9=lag9(pflo); if (gvkey NE lag9(gvkey)) or ((year-9) NE lag9(year)) then pflolag9=.; pflolag10=lag10(pflo); if (gvkey NE lag10(gvkey)) or ((year-10) NE lag10(year)) then pflolag10=.; Att1=0; if pflo=pflolag1 then Att1=1; Att2=0; if pflo=pflolag1=pflolag2 then Att2=1; Att3=0; if pflo=pflolag1=pflolag2=pflolag3 then Att3=1; Att4=0; if pflo=pflolag1=pflolag2=pflolag3=pflolag4 then Att4=1; Att5=0; if pflo=pflolag1=pflolag2=pflolag3=pflolag4=pflolag5 then Att5=1; Att6=0; if pflo=pflolag1=pflolag2=pflolag3=pflolag4=pflolag5=pflolag6 then Att6=1; Att7=0; if pflo=pflolag1=pflolag2=pflolag3=pflolag4=pflolag5=pflolag6=pflolag7 then Att7=1; Att8=0; if pflo=pflolag1=pflolag2=pflolag3=pflolag4=pflolag5=pflolag6=pflolag7=pflolag8 then Att8=1; Att9=0; if pflo=pflolag1=pflolag2=pflolag3=pflolag4=pflolag5=pflolag6=pflolag7=pflolag8=pflolag9 then Att9=1; Att10=0; if pflo=pflolag1=pflolag2=pflolag3=pflolag4=pflolag5=pflolag6=pflolag7=pflolag8=pflolag9=pflolag10 then Att10=1; if year<1964 then delete; if year>2011 then delete; run; proc means data=Att1 noprint; var Att1 Att2 Att3 Att4 Att5 Att6 Att7 Att8 Att9 Att10 ; output out=Att2 sum=; run; data Att_out; set Att1; keep gvkey datadate year pflol pflolag1 pflolag2 pflolag3 pflolag4 pflolag5 pflolag6 pflolag7 pflolag8 pflolag9 pflolag10 Att1 Att2 Att3 Att4 Att5 Att6 Att7 Att8 Att9 Att10; run; %macro winsor(dsetin = portfolio7, dsetout = portfolio8, byvar = YEAR, vars = E1 E1lag1 BVE1lag1 BVE1lag2 , type=winsor, pctl=1 99); %if &dsetout = %then %let dsetout = &dsetin; %let varL=; %let varH=; %let xn=1; %do %until ( %scan(&vars,&xn)= ); %let token = %scan(&vars,&xn); %let varL = &varL &token.L; %let varH = &varH &token.H; %let xn=%EVAL(&xn + 1); %end; %let xn=%eval(&xn-1); data xtemp; set &dsetin; run; %let dropvar = ; %if &byvar = none %then %do; data xtemp; set xtemp; xbyvar = 1; run; %let byvar = xbyvar; %let dropvar = xbyvar; %end; proc sort data = xtemp; by &byvar; run; proc univariate data = xtemp noprint; by &byvar; var &vars; output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H; run; data &dsetout; merge xtemp xtemp_pctl; by &byvar; array trimvars{&xn} &vars; array trimvarl{&xn} &varL; array trimvarh{&xn} &varH; do xi = 1 to dim(trimvars); %if &type = winsor %then %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi}; if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi}; end; %end; %else %do; if trimvars{xi} ne . then do; if (trimvars{xi} < trimvarl{xi}) then delete; if (trimvars{xi} > trimvarh{xi}) then delete; end; %end; end; drop &varL &varH &dropvar xi; run; %mend winsor; run; %winsor; proc sort data=portfolio8; by pflo year ; run; proc means data=portfolio8 noprint; by pflo year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ; output out=portfolio9 sum=; run; data portfolio10; set portfolio9; E=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table portfolio11 as select distinct b.*, a.RET, a.AGGE, a.TFP, a.wealth_D1, a.wealth_RD1 from work.portfolio10 as b left join extl.wealth6 as a on (b.link_year=a.year); quit; proc sort data=portfolio8; by year ; run; proc means data=portfolio8 noprint; by year ; var E1 E1lag1 MVElag2 BVE1lag1 BVE1lag2 ; output out=MKT sum=; run; data MKT2; set MKT; MKT=(E1-E1lag1)/MVElag2; link_year=year; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table portfolio12 as select distinct b.*, a.MKT, a.MKT2, a.MKT3 from work.portfolio11 as b left join work.MKT2 as a on (b.year=a.link_year); quit; * portfolio t-s regression; data portfolio13; set portfolio12; if year<1964 then delete; if year>2011 then delete; if pflo=. then delete; run; proc sort data=portfolio13; by pflo; run; ods listing close; proc reg data=portfolio13 ADJRSQ outest=portfolio14 TABLEOUT noprint; by pflo; model E = wealth_D1 TFP /SPEC ACOV ; run; data portfolio15; set portfolio14; if _TYPE_~='PARMS' then delete; wealth_beta=wealth_D1; TFP_beta=TFP; Intercept_two=Intercept; run; proc sort data=portfolio13; by pflo; run; ods listing close; proc reg data=portfolio13 ADJRSQ outest=portfolio14a TABLEOUT noprint; by pflo; model E = MKT /SPEC ACOV ; run; data portfolio15a; set portfolio14a; if _TYPE_~='PARMS' then delete; MKT_beta=MKT; Intercept_one=Intercept; run; proc sql ; create table portfolio15b as select distinct b.*, a.MKT_beta, a.Intercept_one from work.portfolio15 as b left join work.portfolio15a as a on (b.pflo=a.pflo); quit; ** summary statistics on betas in Table 7; proc means data=portfolio15b n nmiss mean min p10 p25 p50 p75 p90 max std; var wealth_beta TFP_beta MKT_beta ; run; proc corr data=portfolio15b pearson spearman outp=p_correlation outs=s_correlation; var wealth_beta TFP_beta MKT_beta ; run; * link with returns ; data CRSP2; set CRSP1; link_year=.; if month=7 or month=8 or month=9 or month=10 or month=11 or month=12 then link_year=year; if month=1 or month=2 or month=3 or month=4 or month=5 or month=6 then link_year=year-1; llink_year=put(link_year,4.); drop link_year; rename llink_year=link_year; run; proc sql ; create table portfolio16 as select distinct b.*, a.pflo, a.BMR, a.MMTR, a.sizeR, a.ATGR, a.BMlag1, a.MMT, a.ATGAlag1, a.MVElag1 from work.CRSP2 as b left join work.portfolio7 as a on (b.permno=a.permno and b.link_year=a.year ) order by b.permno, b.date; quit; data portfolio16a; set portfolio16; if pflo=. then delete; run; proc sql ; create table portfolio17 as select distinct b.*, a.wealth_beta, a.TFP_beta, a.MKT_beta from work.portfolio16a as b left join work.portfolio15b as a on (b.pflo=a.pflo); quit; data portfolio18; set portfolio17; m2='12'; if month=1 then m2='01'; if month=2 then m2='02'; if month=3 then m2='03'; if month=4 then m2='04'; if month=5 then m2='05'; if month=6 then m2='06'; if month=7 then m2='07'; if month=8 then m2='08'; if month=9 then m2='09'; if month=10 then m2='10'; if month=11 then m2='11'; M=compress(year||m2); if year<1964 then delete; if year>2011 then delete; lBMlag1=log(BMlag1); lMVElag1=log(MVElag1); run; proc means data=portfolio18 mean min p10 p25 p50 p75 p90 max std; var lBMlag1 MMT ATGAlag1 lMVElag1 ; run; proc corr data=portfolio18 pearson spearman outp=p_correlation outs=s_correlation; var MKT_beta TFP_beta wealth_beta lBMlag1 MMT lMVElag1 ATGAlag1 ; run; ****** Fama MacBeth regressions in Table 8; * Model 1; proc sort data= portfolio18; by M; run; proc reg data=portfolio18 ADJRSQ outest=RPM3 TABLEOUT noprint; by M; model R = MKT_beta /SPEC ACOV; run; data RPM4; set RPM3; if _TYPE_~='PARMS' then delete; run; proc means data=RPM4 mean std t probt; var Intercept MKT_beta _ADJRSQ_; title 'MKT_beta '; run; * Model 2; proc sort data= portfolio18; by M; run; proc reg data=portfolio18 ADJRSQ outest=RPM3 TABLEOUT noprint; by M; model R = wealth_beta TFP_beta /SPEC ACOV; run; data RPM4; set RPM3; if _TYPE_~='PARMS' then delete; run; proc means data=RPM4 mean std t probt; var Intercept wealth_beta TFP_beta _ADJRSQ_; title 'wealth_beta TFP_beta '; run; * Model 3; proc sort data= portfolio18; by M; run; proc reg data=portfolio18 ADJRSQ outest=RPM3 TABLEOUT noprint; by M; model R = lBMlag1 MMT ATGAlag1 lMVElag1 /SPEC ACOV; run; data RPM4; set RPM3; if _TYPE_~='PARMS' then delete; run; proc means data=RPM4 mean std t probt; var Intercept lBMlag1 MMT ATGAlag1 lMVElag1 _ADJRSQ_ _RMSE_ _EDF_; run; * Model 4; proc sort data= portfolio18; by M; run; proc reg data=portfolio18 ADJRSQ outest=RPM3a TABLEOUT noprint; by M; model R = wealth_beta TFP_beta /SPEC ACOV; run; data RPM4a; set RPM3a; if _TYPE_~='PARMS' then delete; wealth_betac=wealth_beta; TFP_betac=TFP_beta; run; proc sql ; create table portfolio18a as select distinct b.*, a.wealth_betac, a.TFP_betac, a.Intercept from work.portfolio18 as b left join work.RPM4a as a on (b.M=a.M); quit; data portfolio18d; set portfolio18a; error=R-Intercept-wealth_betac*wealth_beta-TFP_betac*TFP_beta; run; proc sort data= portfolio18d; by M; run; proc reg data=portfolio18d ADJRSQ outest=RPM3b TABLEOUT noprint; by M; model error = lBMlag1 MMT ATGAlag1 lMVElag1 /SPEC ACOV; run; data RPM4b; set RPM3b; if _TYPE_~='PARMS' then delete; run; proc means data=RPM4b mean std t probt; var Intercept lBMlag1 MMT ATGAlag1 lMVElag1 _ADJRSQ_ _RMSE_ _EDF_; run;