/********************************************************************* */
/*  Program        : 7.MakeEGpredictordispersion.sas                           */
/*  Description    : Construct the annual cross-sectional dispersions      */
/*                   of the EG predictors of Hou et al. (2021)                       */
/*                   (Table 7 of the paper, estimated by                                */
/*                   Table7\7.Regression.do):                                               */
/*                   cop_std    = CS standard deviation of operating              */
/*                                cash flows (DISCoP)                                        */
/*                   tobinq_std = CS standard deviation of Tobin's q             */
/*                                (DISQ)                                                             */
/*                   d_roe_stdq = CS standard deviation of the change          */
/*                                in ROE (DISdROE), computed from the          */
/*                                earliest quarterly announcement                       */
/*                                (rdq) of each firm-year                                    */
/*                   The dispersions are merged with the uncertainty            */
/*                   levels (dpca_hat = macro, dpca_res = micro, and          */
/*                   the total uncertainty).                                                    */
/*                   The final dataset corresponds to the included                */
/*                   Table7\eia_input1_negativeprc2.xlsx; no export            */
/*                   statement is needed because the xlsx file is                    */
/*                   provided in the package.                                               */
/*                                                                                                       */
/*  Input : output\q5\ crsp_compustat3 (monthly firm-level data with    */
/*          the EG predictors, from 0.Q5factorconstruction.sas)               */
/*          data\   monthret_ff (CRSP monthly returns, from                   */
/*          0.DataDownload.sas)                                                             */
/*          output\ dispersion_all (uncertainty levels, from                      */
/*          0.MakeDispersion.sas)                                                          */
/*  Output: output\ eia_input1 (SAS dataset with the columns of         */
/*          Table7\eia_input1_negativeprc2.xlsx)                                    */
/********************************************************************* */

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

/*1.Extract the EG predictors from the monthly firm-level data*/
data myout.q5_input(keep = permno gvkey date datadate datadateq rdq fyear fqtr fyr d1_ia cop tobinq d_roe be cym); set myq5.crsp_compustat3; run;
data myout.q5_input; set myout.q5_input;
cyear=year(datadate);
rdq2=coalesce(rdq,datadateq); format rdq2 date9.; run;
proc sql;
create table myout.q5_input as
select a.*, b.prc
from myout.q5_input as a, mydisp.monthret_ff as b
where a.permno=b.permno and a.date=b.date and b.prc>=1
order by permno, date;
quit;

/*2.Cross-sectional standard deviations of the annual EG predictors*/
data myout.q5_input2; set myout.q5_input; run;
PROC SORT DATA=myout.q5_input2
 NODUPKEY ;
 BY gvkey fyear;
RUN ;
proc sort data=myout.q5_input2; by cyear; run;
proc univariate data=myout.q5_input2 noprint;
     var d1_ia cop tobinq d_roe;
     by cyear;
     output out=myout.q5_input3 std=d1_ia_std cop_std tobinq_std d_roe_stdy;
run;

/*3.Cross-sectional standard deviation of the quarterly change in ROE*/
/*Given firm and cyear, extract the oldest d_roe (the earliest announcement)*/
proc sort data=myout.q5_input; by permno rdq2; run;
proc sql;
create table myout.q5_droe as
select *
from myout.q5_input
group by permno, cyear
having rdq2=min(rdq2);
quit;
PROC SORT DATA=myout.q5_droe
 NODUPKEY ;
 BY permno cyear rdq2;
RUN ;
proc sort data=myout.q5_droe; by cyear; run;
proc univariate data=myout.q5_droe noprint;
     var d_roe;
     by cyear;
     output out=myout.q5_inputq std=d_roe_stdq;
run;

/*4.Merge the dispersions of the EG predictors with the uncertainty levels*/
/*The total uncertainty level of the paper (dtfp_std_star) is named dtfp_std*/
/*to match the included xlsx file; it is not reported in Table 7.*/
proc sql;
create table myout.eia_input1 as
select a.cyear, a.cop_std, a.tobinq_std, b.d_roe_stdq,
       c.dpca_hat, c.dpca_res, c.dtfp_std_star as dtfp_std
from myout.q5_input3 as a, myout.q5_inputq as b, myout.dispersion_all as c
where a.cyear=b.cyear=c.cyear and 1972 <= a.cyear <= 2016
order by a.cyear;
quit;
proc export data=myout.eia_input1 outfile="&projpath.\Table7\eia_input1_negativeprc2.xlsx" replace;
run;
