/********************************************************************* */
/*  Program        : 1.MakeAnnualfactors.sas                                    */
/*  Description    : Construct the annualized pricing factors and       */
/*                   match them with the uncertainty shocks                    */
/*                   (Table 1 of the paper).                                              */
/*                   The Fama-French factors are annualized here by      */
/*                   compounding the monthly returns of the 2x3            */
/*                   component portfolios. The q-factors and the EG       */
/*                   factor are annualized in 0.Qfactorconstruction.sas    */
/*                   and 0.Q5factorconstruction.sas, respectively.           */
/*                   The uncertainty shocks are matched with a               */
/*                   six-month reporting gap.                                          */
/*                   The correlations are computed in Stata                     */
/*                   (Table1\correlation.do).                                           */
/*                                                                                                   */
/*  Input : data\ 6sizebm.xlsx, 6sizeinv.xlsx, 6sizeop.xlsx,               */
/*          6sizemom.xlsx (monthly returns in percent of the 2x3        */
/*          size/BM, size/INV, size/OP, and size/MOM portfolios,      */
/*          downloaded from Kenneth French's data library)               */
/*          data\ ff5 (from 0.DataDownload.sas: mktrf, rf)                  */
/*          data\ dispersion_all (from 0.MakeDispersion.sas:               */
/*          d_tfpstd_star, d_pcahat, d_pcares)                                      */
/*          output\ Q_factory (from 0.Qfactorconstruction.sas)            */
/*          output\q5\ EIA_factory (from 0.Q5factorconstruction.sas)  */
/*          output\misp\ MISP_factory (from 0.Mispricingfactor.sas)   */
/*  Output: Table1\annualfactors.dta                                                 */
/********************************************************************* */

%let projpath = D:\SAS\TFP\Dispersion_replication;
libname mydisp "&projpath.\data";
libname myq "&projpath.\output";
libname myq5 "&projpath.\output\q5";
libname mymisp "&projpath.\output\misp";

/*1.Import the Fama-French 2x3 component portfolios*/
PROC IMPORT datafile="&projpath.\data\6sizebm.xlsx"
 	OUT=mydisp.size_bm
 	DBMS=xlsx
 	REPLACE;
 	GETNAMES=Yes;
RUN;
proc sql;
     create table mydisp.size_bm(drop= sl sm sh bl bm bh) as
     select a.*, sl/100 as sl1, sm/100 as sm1, sh/100 as sh1, bl/100 as bl1, bm/100 as bm1, bh/100 as bh1
     from mydisp.size_bm as a
     where 196301 <= var1 <= 201812;
quit;
data mydisp.size_bm(rename=(sl1=sl)rename=(sm1=sm)rename=(sh1=sh)rename=(bl1=bl)rename=(bm1=bm)rename=(bh1=bh));
     set mydisp.size_bm;
run;
PROC IMPORT datafile="&projpath.\data\6sizeinv.xlsx"
 	OUT=mydisp.size_inv
 	DBMS=xlsx
 	REPLACE;
 	GETNAMES=Yes;
RUN;
proc sql;
     create table mydisp.size_inv(drop= sl sm sh bl bm bh) as
     select a.*, sl/100 as sl1, sm/100 as sm1, sh/100 as sh1, bl/100 as bl1, bm/100 as bm1, bh/100 as bh1
     from mydisp.size_inv as a
     where 196301 <= var1 <= 201812;
quit;
data mydisp.size_inv(rename=(sl1=sl)rename=(sm1=sm)rename=(sh1=sh)rename=(bl1=bl)rename=(bm1=bm)rename=(bh1=bh));
     set mydisp.size_inv;
run;
PROC IMPORT datafile="&projpath.\data\6sizeop.xlsx"
 	OUT=mydisp.size_op
 	DBMS=xlsx
 	REPLACE;
 	GETNAMES=Yes;
RUN;
proc sql;
     create table mydisp.size_op(drop= sl sm sh bl bm bh) as
     select a.*, sl/100 as sl1, sm/100 as sm1, sh/100 as sh1, bl/100 as bl1, bm/100 as bm1, bh/100 as bh1
     from mydisp.size_op as a
     where 196301 <= var1 <= 201812;
quit;
data mydisp.size_op(rename=(sl1=sl)rename=(sm1=sm)rename=(sh1=sh)rename=(bl1=bl)rename=(bm1=bm)rename=(bh1=bh));
     set mydisp.size_op;
run;
PROC IMPORT datafile="&projpath.\data\6sizemom.xlsx"
 	OUT=mydisp.size_mom
 	DBMS=xlsx
 	REPLACE;
 	GETNAMES=Yes;
RUN;
proc sql;
     create table mydisp.size_mom(drop= sl sm sh bl bm bh) as
     select a.*, sl/100 as sl1, sm/100 as sm1, sh/100 as sh1, bl/100 as bl1, bm/100 as bm1, bh/100 as bh1
     from mydisp.size_mom as a
     where 196301 <= var1 <= 201812;
quit;
data mydisp.size_mom(rename=(sl1=sl)rename=(sm1=sm)rename=(sh1=sh)rename=(bl1=bl)rename=(bm1=bm)rename=(bh1=bh));
     set mydisp.size_mom;
run;

/*2.Compounding monthly returns*/
proc printto log = junk; run;
proc expand data = mydisp.size_bm out=mydisp.size_bmy method=none;
    id var1;
    convert sl = sly  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sm = smy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sh = shy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bl = bly  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bm = bmy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bh = bhy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
quit;
proc printto; run;
data mydisp.size_bmy2(keep=var1 year month SMBy1 HMLy);
     set mydisp.size_bmy;
	 small=(sly+smy+shy)/3;
	 big=(bly+bmy+bhy)/3;
	 SMBy1=small - big;
     low=(sly+bly)/2;
	 high=(shy+bhy)/2;
	 HMLy=high - low;
run;
proc printto log = junk; run;
proc expand data = mydisp.size_inv out=mydisp.size_invy method=none;
    id var1;
    convert sl = sly  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sm = smy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sh = shy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bl = bly  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bm = bmy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bh = bhy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
quit;
proc printto; run;
data mydisp.size_invy2(keep=var1 year month SMBy2 CMAy);
     set mydisp.size_invy;
	 small=(sly+smy+shy)/3;
	 big=(bly+bmy+bhy)/3;
	 SMBy2=small - big;
     low=(sly+bly)/2;
	 high=(shy+bhy)/2;
	 CMAy=low - high;
run;
proc printto log = junk; run;
proc expand data = mydisp.size_op out=mydisp.size_opy method=none;
    id var1;
    convert sl = sly  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sm = smy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sh = shy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bl = bly  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bm = bmy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bh = bhy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
quit;
proc printto; run;
data mydisp.size_opy2(keep=var1 year month SMBy3 RMWy);
     set mydisp.size_opy;
	 small=(sly+smy+shy)/3;
	 big=(bly+bmy+bhy)/3;
	 SMBy3=small - big;
     low=(sly+bly)/2;
	 high=(shy+bhy)/2;
	 RMWy=high - low;
run;
proc printto log = junk; run;
proc expand data = mydisp.size_mom out=mydisp.size_momy method=none;
    id var1;
    convert sl = sly  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sm = smy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sh = shy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bl = bly  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bm = bmy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert bh = bhy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
quit;
proc printto; run;
data mydisp.size_momy2(keep=var1 year month UMDy);
     set mydisp.size_momy;
	 low=(sly+bly)/2;
	 high=(shy+bhy)/2;
	 UMDy=high - low;
run;
/*Annualized market factor and risk-free rate*/
data mydisp.mkt(keep = var1 year month mktrf rf mkt);
     set mydisp.ff5;
	 mkt = mktrf+rf;
run;
proc printto log = junk; run;
proc expand data = mydisp.mkt out=mydisp.mkty method=none;
    id var1;
    convert rf = rfy        / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert mkt = mkty      / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
quit;
proc printto; run;
data mydisp.mkty(keep=var1 year month mktrfy mkty rfy);
     set mydisp.mkty;
	 mktrfy=mkty - rfy;
run;
/*Annualized Fama-French factors*/
proc sql;
    create table mydisp.ffy as
    select a.var1, a.year, a.month, a.HMLy, b.CMAy, c.RMWy, mean(a.SMBy1, b.SMBy2, c.SMBy3) as SMBy, d.UMDy, e.mktrfy, e.mkty
    from mydisp.size_bmy2 as a, mydisp.size_invy2 as b, mydisp.size_opy2 as c, mydisp.size_momy2 as d, mydisp.mkty as e
    where a.var1 = b.var1 = c.var1 = d.var1 = e.var1
    and 196309 <= a.var1 <=201812;
quit;

/*3.Merge with the annualized q-factors and the EG factor*/
proc sql;
    create table mydisp.ffy as
    select a.*, b.q_mey, b.q_IAy, b.q_ROEy
    from mydisp.ffy as a, myq.Q_factory as b
    where year(b.date)=a.year
	and month(b.date)=a.month;
quit;
proc sql;
    create table mydisp.ffy as
    select a.*, b.EIA
    from mydisp.ffy as a, myq5.EIA_factory as b
    where b.year=a.year
	and b.month=a.month;
quit;

/*4.Merge with the annualized mispricing factor*/
/*A left join is used so that the shorter MISP sample does not reduce*/
/*the overall sample.*/
proc sql;
    create table mydisp.ffy as
    select a.*, b.MISP
    from mydisp.ffy as a left join mymisp.MISP_factory as b
    on year(b.date)=a.year
	and month(b.date)=a.month;
quit;

/*5.Match the uncertainty shocks with the six-month reporting gap*/
proc sql;
     create table mydisp.annualfactors as
	 select a.year, a.month, a.mktrfy, a.SMBy, a.HMLy, a.CMAy, a.RMWy, a.UMDy,
	        a.q_mey, a.q_IAy, a.q_ROEy, a.EIA, a.MISP,
	        b.d_tfpstd_star, b.d_pcahat, b.d_pcares
	 from mydisp.ffy as a, myq.dispersion_all as b
     where a.year=b.cyear+2
	 and b.cyear>=1972 and b.cyear<=2017
     and a.month=6;
quit;
proc sort data=mydisp.annualfactors;
by year;
run;
proc export data=mydisp.annualfactors outfile="&projpath.\Table1\annualfactors.dta" replace;
run;
