/********************************************************************* */
/*  Program        : 6.MakeFirmlevelpanel.sas                           */
/*  Description    : Construct the firm-month panel used in the panel   */
/*                   regressions of firm-level productivity growth on   */
/*                   the lagged uncertainty shocks (Table 6 of the      */
/*                   paper, estimated by Table6\6.Regression.do).       */
/*                                                                      */
/*  Input : output\q5\ crsp_compustat4                                  */
/*          (monthly firm-level data with exp_ia and the firm           */
/*          characteristics, from 0.Q5factorconstruction.sas)           */
/*          output\ TFP.csv (firm-level TFP estimates, from             */
/*          0.TFPestimation.do)                                         */
/*          output\ dispersion_all (annual uncertainty shocks, from     */
/*          0.MakeDispersion.sas)                                       */
/*  Output: Table6\ portret10_unc_pca.dta                               */
/*          (the input of Table6\6.Regression.do)                       */
/********************************************************************* */

%let projpath = D:\SAS\TFP\Dispersion_replication;
libname mydisp "&projpath.\data";
libname myq5 "&projpath.\output\q5";
libname myout "&projpath.\output";
/*1.Firm-level TFP estimates*/
PROC IMPORT datafile="&projpath.\output\TFP.csv"
 	OUT=myout.tfp6
 	DBMS=csv
 	REPLACE;
 	GETNAMES=Yes;
RUN;
/*Gvkey format change: Number to Character*/
data myout.tfp6;
     set myout.tfp6;
     gvkey1=put(gvkey,z6.);
     drop gvkey;
     rename gvkey1=gvkey;
run;
data myout.tfp6;
     retain gvkey;
     set myout.tfp6;
	 If d_TFPpool =. then delete;
run;
proc sort data=myout.tfp6;
     by gvkey cyear;
run;
proc sql;
create table myout.tfp6 as
select a.*, b.fyear
from myout.tfp6 as a, myout.compustat_funda as b
where a.gvkey=b.gvkey
and a.cyear=year(b.datadate)
order by gvkey , datadate;
quit;

/*2.Merge the next fiscal year TFP growth to the monthly firm-level data*/
proc sql;
create table myout.Eia_unc3 as
select a.*, b.d_TFPpool
from myq5.crsp_compustat4 as a, myout.tfp6 as b
where a.gvkey=b.gvkey
and a.fyear=b.fyear-1
order by date , permno;
quit;

/*3.Construct the EG decile portfolios*/
proc univariate data=myout.Eia_unc3 noprint;
where shrcd in (10,11) and exchcd in (1,2,3);
var exp_ia;
by date;
output out=myout.nyse_eia  pctlpts=10 20 30 40 50 60 70 80 90 pctlpre=Eia;
run;
proc sql;
create table myout.Eia10 as
select a.permno, a.date, a.fyear, a.me, a.lme, a.wi, a.d1_ia, a.cym, a.exp_ia, a.f1_ia,a.ia,
       b.eia10, b.eia20, b.eia30, b.eia40, b.eia50, b.eia60, b.eia70, b.eia80, b.eia90, a.cop, a.tobinq, a.d_roe, a.l_cop, a.l_tobinq, a.l_droe,a.siccd,
	    a.d_tfppool
from myout.Eia_unc3 as a, myout.nyse_eia as b
where a.date=b.date;
quit;
data myout.PF_decile;
     set myout.Eia10;
     If eia90 <= exp_ia                 then EIAport = '10';
     If exp_ia<  eia10                  then EIAport = '1';
     If eia10 <= exp_ia < eia20         then EIAport = '2';
     If eia20 <= exp_ia < eia30         then EIAport = '3';
     If eia30 <= exp_ia < eia40         then EIAport = '4';
     If eia40 <= exp_ia < eia50         then EIAport = '5';
     If eia50 <= exp_ia < eia60         then EIAport = '6';
     If eia60 <= exp_ia < eia70         then EIAport = '7';
     If eia70 <= exp_ia < eia80         then EIAport = '8';
     If eia80 <= exp_ia < eia90         then EIAport = '9';
if missing(EIAport)=0 then nonmissport=1;
else nonmissport=0;
keep permno date fyear exp_ia EIAport nonmissport d_tfppool cop d_roe tobinq l_cop l_droe l_tobinq lme siccd ia;
run;

/*4.Attach the June dates to the annual uncertainty shocks*/
/*Due to all annual correlation coefficients are based on June*/
data myout.unc_dated;
     set myout.dispersion_all;
     where 1972 <= cyear <= 2017;
     date=mdy(6,30,cyear+1);
     format date date9.;
     keep cyear date d_tfpstd_star d_pcahat d_pcares;
run;

/*5.Merge the uncertainty shocks to the firm-month panel and export*/
proc sql;
create table myout.portret10_unc_pca as
select a.*, b.d_tfpstd_star, b.d_pcahat, b.d_pcares, b.date as uncdate, year(a.date) as year
from myout.PF_decile as a, myout.unc_dated as b
where intck('month',b.date,a.date) between -11 and 0;
quit;
proc export data=myout.portret10_unc_pca outfile="&projpath.\Table6\portret10_unc_pca.dta" replace;
run;
