/********************************************************************* */
/*  Program        : 0.Q5factorconstruction.sas                                     */
/*  Description    : Construct the expected investment growth (EG)      */
/*                   factor of the Hou, Mo, Xue, and Zhang (2021)              */
/*                   q^5 model, monthly and annualized.                              */
/*                                                                                                        */
/*  Input : WRDS (quarterly and annual Compustat; signon required)   */
/*          data\ SAS datasets from 0.DataDownload.sas:                         */
/*          monthret_ff, ccmxpf_linktable                                                */
/*          output\ SAS datasets from 0.Qfactorconstruction.sas:              */
/*          factors (q-factors merged with FF factors)                              */
/*  Output: output\q5\ SAS datasets:                                                      */
/*          EIA_factor  (monthly EG factor)                                             */
/*          EIA_factory (annualized EG factor)                                         */
/********************************************************************* */

/************************************************************************/
/*Three data sets: Monthly CRSP + Annual Compustat + Quarterly Compustat                 */
/*                 From July 1963 to December 2016                                                             */
/*               : -Financial firms & Firms with negative book equity                                     */
/************************************************************************/

/*****************************************************************************/
/*1. Annual Compustat: AT,SEQ,CEQ,PSTK,LT,TXDITC,PSTKRV,PSTKL,PSTK,DLTT,DLC,*/
/*                     REVT,COGS,XSGA,XRD,RECT,INVT,XPP,DRC,DRLT,AP,XACC                 */
/*2. Quarterly Compustat: IBQ, TXDITCQ, SEQQ, CEQQ, PSTKQ, ATQ, LTQ, RDQ,              */
/*                        PSTKRQ                                                                                                     */
/*3. CRSP: Monthly return, Prices                                                                                           */
/*****************************************************************************/

%let projpath = D:\SAS\TFP\Dispersion_replication;
libname myq5 "&projpath.\output\q5";
libname myq "&projpath.\output";
libname mydisp "&projpath.\data";
/*1. Download data */ 
/*1-1. Quarterly Compustat */ 
%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=TCP remote=WRDS;
signon username=_prompt_;
rsubmit;
%let beg_date = '01JAN1960'd;
%let end_date = '31DEC2018'd;
libname compm '/wrds/comp/sasdata/nam';
proc sql;
     create table comp1(keep = fyearq fqtr fyr datadate gvkey datacqtr atq ibq seqq ceqq pstkq ltq rdq txditcq pstkrq dvpsxq cshoq ajexq) as
	 select a.* from compm.fundq a
	 where indfmt='INDL' 
     and datafmt='STD' 
     and popsrc='D' 
     and consol='C'
     and (&beg_date <= datadate <= &end_date) ;
quit;
data comp1;
set comp1 (keep = fyearq fqtr fyr datadate gvkey datacqtr atq ibq seqq ceqq pstkq ltq rdq txditcq pstkrq dvpsxq cshoq ajexq);
if fyr > 0;
if fyr <= 5 then cyear = fyearq + 1;
else cyear = fyearq;

/*begin and end dates for fiscal year;*/
date1 = MDY(fyr,1,cyear);
endfyr = intnx('month',date1,0,'end'); format endfyr date9.;
begfyr = intnx('month',endfyr,-11,'beg'); format begfyr date9.;

keep gvkey begfyr endfyr fyearq fqtr fyr datadate datacqtr atq ibq seqq ceqq pstkq ltq rdq txditcq pstkrq dvpsxq cshoq ajexq;
run;
proc download data=comp1 out=myq5.compustat_fundq1;
run;
endrsubmit;
proc sort data=myq5.compustat_fundq1;
by gvkey fyearq fqtr;
run;
/*1-2. Annual Compustat */
rsubmit;
%let beg_date = '01JAN1960'd;
%let end_date = '31DEC2018'd;
libname compm '/wrds/comp/sasdata/nam';
proc sql;
     create table comp3(keep = fyear gvkey datadate fyr AT SEQ CEQ PSTK LT TXDITC PSTKRV PSTKL PSTK DLTT DLC REVT COGS XSGA XRD RECT INVT XPP DRC DRLT AP XACC AJEX csho sich) as
	 select a.*  from compm.funda a
	 where indfmt='INDL' 
     and datafmt='STD' 
     and popsrc='D' 
     and consol='C'
     and (&beg_date <= datadate <= &end_date) ;
quit;
proc download data=comp3 out=myq5.compustat_funda1;
run;
endrsubmit;
signoff;
proc sort data=myq5.compustat_funda1 out=myq5.compustat_funda1;
  by gvkey descending fyear;
run;
data myq5.compustat_funda1;
  set myq5.compustat_funda1;
  by gvkey descending fyear;
  retain sic;
  if first.gvkey | not missing(sich) then
  sic=sich;
run;
proc sort data=myq5.compustat_funda1;
  by gvkey fyear;
run;
data myq5.compustat_funda1;
  set myq5.compustat_funda1;
  by gvkey fyear;
  retain sic1;
  if first.gvkey | not missing(sic) then
  sic1=sic;
run;
proc sort data=myq5.compustat_funda1;
  by gvkey fyear;
run;
data myq5.compustat_funda1(drop = sic);
     set myq5.compustat_funda1;
     rename sic1=sic;
run;
/*Add sic to quarterly compustat*/
proc sql;
create table myq5.compustat_fundq as
select a.*, b.sic
from myq5.compustat_fundq1 as a left join myq5.compustat_funda1 as b 
on a.gvkey=b.gvkey and a.fyearq = b.fyear;
quit;
data myq5.compustat_fundq;
     set myq5.compustat_fundq;
     by gvkey fyearq fqtr;
	 PS = coalesce(pstkrq,pstkq,0);
	 SH = coalesce(seqq,ceqq+pstkq,atq-ltq);
	 if missing(txditcq) then txditcq=0;
	 BE1 = SH + txditcq - PS;
	 if sic<6000 or sic>6999;
run;
/*Make fyear-fquarter series (fyq)*/
data myq5.compustat_fundq;
  set myq5.compustat_fundq;
  fdate = yyq(fyearq,fqtr);
  format fdate date9.; 
  fyq = intck("qtr",'1jan1960'd,fdate);
  by gvkey;
  fyq_n = dif(fyq);
  if first.gvkey then fyq_n=.;
run;
/*Some variables are imputed from annual Compustant if missing*/
proc sql;
     create table myq5.compustat_fundqa as
     select a.*, b.PSTKRV, b.PSTKL, b.PSTK, b.SEQ, b.CEQ, b.PSTK as pstky, b.AT, b.LT, b.TXDITC, b.AJEX, b.csho
	 from myq5.compustat_fundq as a left join myq5.compustat_funda1 as b
	 on a.gvkey = b.gvkey and a.fyearq = b.fyear;
quit;
data myq5.compustat_fundqa;
     set myq5.compustat_fundqa;
	 PSy = coalesce(PSTKRV,PSTKL,PSTKy,0);
	 SHy = coalesce(SEQ,CEQ+PSTKy,AT-LT);
	 if missing(TXDITC) then TXDITC=0;
	 if fqtr=4 & year(datadate)<=1971 then BE2 = SHy + TXDITC - PSy;
	 if ajexq=. and fqtr=4 and ajex ne . then ajexq=ajex;
	 if cshoq=. and fqtr=4 and csho ne . then cshoq=csho;
run;
proc sql;
create table myq5.compustat_fundqa2
as select * from myq5.compustat_fundqa as a, mydisp.ccmxpf_linktable as b
where a.gvkey = b.gvkey
and b.LINKTYPE in ("LU","LC","LD","LF","LN","LS")
/*LO: links to trading company but no linking trading issues exists.*/
/*LX: links to foreign company with an issue trading on US exchanges.*/
and b.LINKPRIM in ("P", "C")
/*LO: links to trading company but no linking trading issues exists.*/
/*LX: links to foreign company with an issue trading on US exchanges.*/
/*Entire fiscal years within link date range*/
and (b.LINKDT<=a.endfyr or b.LINKDT = .B)
and (a.endfyr<=b.LINKENDDT or b.LINKENDDT=.E);
quit;
/*CRSP monthly return */
/*mydisp.monthret_ff: 1963 to 2018*/
proc sql;
create table myq5.compustat_fundqa2 as                 
select a.*, b.shrout, b.cfacshr, b.siccd
from myq5.compustat_fundqa2 as a , mydisp.monthret_ff as b
where a.lpermno=b.permno
and year(a.datadate) = year(b.date) and month(a.datadate)=month(b.date)
order by a.gvkey, a.fyq;
quit;
data myq5.compustat_fundqa3; set myq5.compustat_fundqa2;
     if missing(dvpsxq) then dvpsxq=0;
run;
proc sql;
create table myq5.compustat_fundqa3 as                 
select a.*, b.cshoq as l_cshoq, b.shrout as l_shrout
from myq5.compustat_fundqa3 as a left join myq5.compustat_fundqa3 as b
on a.gvkey=b.gvkey
and a.fyq -1 = b.fyq
order by a.gvkey, a.fyq;
quit;
data myq5.compustat_fundqa3; set myq5.compustat_fundqa3;
     div1=dvpsxq*l_cshoq*1000000*ajexq;
	 div2=dvpsxq*l_shrout*1000*cfacshr;
run;
data myq5.compustat_fundqa3(drop = div1 div2); set myq5.compustat_fundqa3;
     div=coalesce(div1,div2,0);
run;
proc sql;
create table myq5.compustat_fundqa3 as
select a.* , b.div as l_div, b.be1 as l_be1, b.be2 as l_be2
from myq5.compustat_fundqa3 as a left join myq5.compustat_fundqa3 as b
on a.gvkey = b.gvkey and a.fyq - 1 = b.fyq;
quit;
/* Cleaning Compustat Data for no relevant duplicates  
/* 1. Eliminating overlapping matching : few cases where different gvkeys   */
/*   for same permno-date --- some of them are not 'primary' matches in CCM.*/
/*   Use linkprim='P' for selecting just one gvkey-permno-date combination; */
proc sort data=myq5.compustat_fundqa3 out=myq5.compustat_fundqa3;
          by datadate lpermno descending linkprim gvkey;
run;
data myq5.compustat_fundqa3;
     set myq5.compustat_fundqa3;
     by datadate lpermno descending linkprim gvkey;
     if first.lpermno;
run;
proc sort data=myq5.compustat_fundqa3 nodupkey; by datadate lpermno; run;
/* 2. However, there other type of duplicates within the year                */
/* Some companies change fiscal year end in the middle of the calendar year. */
/* In these cases, there are more than one annual record for accounting data.*/
/* We will be selecting the last annual record in a given calendar year;     */
proc sort data=myq5.compustat_fundqa3; by lpermno fyq datadate ; run;
data myq5.compustat_fundqa3 ;
     set myq5.compustat_fundqa3;
     by lpermno fyq datadate ;
     if last.fyq =1 ;
run;
proc sort data=myq5.compustat_fundqa3 nodupkey; by lpermno datadate; run;
/* End cleaning Compustat        */
/* Estimate book value of equity before 1971*/
/* However, it is not used in our analysis, which starts from 1972*/
data myq5.compustat_fundqa3; set myq5.compustat_fundqa3;
     If year(datadate)<=1971 then l_be3=coalesce(be1, be2) - ibq + div;
run;
data myq5.compustat_fundqa3; set myq5.compustat_fundqa3;
     l_be=coalesce(l_be1, l_be2, l_be3);
run;
proc sql;
create table myq5.compustat_fundqa4 as
select a.* , b.l_be as l2_be, b.ibq as l_ibq
from myq5.compustat_fundqa3 as a left join myq5.compustat_fundqa3 as b
on a.gvkey = b.gvkey and a.fyq - 1 = b.fyq;
quit;
proc sql;
create table myq5.compustat_fundqa4 as
select a.* , b.l_be as l3_be, b.ibq as l2_ibq, b.l_div as l2_div
from myq5.compustat_fundqa4 as a left join myq5.compustat_fundqa4 as b
on a.gvkey = b.gvkey and a.fyq - 2 = b.fyq;
quit;
proc sql;
create table myq5.compustat_fundqa4 as
select a.* , b.l_be as l4_be, b.ibq as l3_ibq, b.l_div as l3_div
from myq5.compustat_fundqa4 as a left join myq5.compustat_fundqa4 as b
on a.gvkey = b.gvkey and a.fyq - 3 = b.fyq;
quit;
data myq5.compustat_fundqa4; set myq5.compustat_fundqa4;
     be41=l_be + ibq - div;
	 be42=l2_be + l_ibq + ibq - l_div - div;
	 be43=l3_be + l2_ibq + l_ibq + ibq - l2_div - l_div - div;
	 be44=l4_be + l3_ibq + l2_ibq + l_ibq + ibq - l3_div - l2_div - l_div - div;
run;
data myq5.compustat_fundqa4; set myq5.compustat_fundqa4;
    If year(datadate)<=1971 then be4=coalesce(be41, be42, be43, be44);
run;
proc sql;
create table myq5.compustat_fundqa4 as
select a.* , b.be4 as l_be4
from myq5.compustat_fundqa4 as a left join myq5.compustat_fundqa4 as b
on a.gvkey = b.gvkey and a.fyq - 1 = b.fyq;
quit;
data myq5.compustat_fundqa4; set myq5.compustat_fundqa4;
     If year(datadate)<=1971 and missing(l_be) then l_be=l_be4;
	 If l_be>0;
run;
data myq5.compustat_fundqa4(drop=l_be4 be4 be44 be43 be42 be41 l3_div l3_ibq l4_be l2_div l2_ibq l3_be l_ibq l2_be l_be3 l_be2 l_be1 
                            CFACSHR shrout be2 csho ajex );
     set myq5.compustat_fundqa4;
     by gvkey;
	 roe=ibq/l_be;
	 year=year(datadate);
	 cqtr=qtr(datadate);
	 
run;
proc sort data=myq5.compustat_fundqa4; by gvkey fyq; run;
proc sql;
create table myq5.compustat_fundqa4 as
select a.* , b.roe as l4_roe
from myq5.compustat_fundqa4 as a left join myq5.compustat_fundqa4 as b
on a.gvkey = b.gvkey and a.fyq - 4 = b.fyq;
quit;
data myq5.compustat_fundqa4;
     set myq5.compustat_fundqa4;
     by gvkey;
	 d_roe=(roe - l4_roe);
	 if missing(d_roe) then d_roe=0;
run;
proc sql;
create table myq5.compustat_fundqa4 as
select a.* , b.d_roe as l_droe
from myq5.compustat_fundqa4 as a left join myq5.compustat_fundqa4 as b
on a.gvkey = b.gvkey and a.fyq - 4 = b.fyq;
quit;
proc sort data=myq5.compustat_fundq; by gvkey fyearq fqtr; run;
/*Annual compustat*/
data myq5.compustat_funda; set myq5.compustat_funda1;
     dr = (DRC + DRLT);
	 if missing(XRD) then XRD=0;
	 if sic<6000 or sic>6999;
run;
proc sql;
create table myq5.compustat_funda as
select a.*, 
       b.at as l_at, b.rect as l_rect, b.invt as l_invt, b.xpp as l_xpp, b.dr as l_dr, b.ap as l_ap, b.xacc as l_xacc
from myq5.compustat_funda as a left join myq5.compustat_funda as b
on a.gvkey = b.gvkey and a.fyear - 1 = b.fyear;
quit;
data myq5.compustat_funda;
     set myq5.compustat_funda;
     by gvkey;
	 if l_at>0;
	 ia=(at-l_at)/l_at;
	 d_RECT=(RECT-l_RECT);
     if missing(d_RECT) then d_RECT=0;
	 d_INVT=(INVT-l_INVT);
     if missing(d_INVT) then d_INVT=0;
	 d_XPP=(XPP-l_XPP);
     if missing(d_XPP) then d_XPP=0;
     d_DR=(DR-l_DR);
     if missing(d_DR) then d_DR=0;
	 d_AP=(AP-l_AP);
     if missing(d_AP) then d_AP=0;
	 d_XACC=(XACC-l_XACC);
     if missing(d_XACC) then d_XACC=0;
	 if at>0;
	 cop=(REVT - COGS - XSGA + XRD - d_RECT - d_INVT - d_XPP + d_DR + d_AP + d_XACC)/AT;
     PS = coalesce(PSTKRV,PSTKL,PSTK,0);
	 SH = coalesce(SEQ,CEQ+PSTK,AT-LT);
	 if missing(TXDITC) then TXDITC=0;
	 BE = SH + TXDITC - PS;
	 if BE >=0;
run;
proc sql;
create table myq5.compustat_funda2 as
select a.gvkey, a.datadate, a.fyear, a.fyr, a.ia, (a.ia - b.ia) as d1_ia, a.DLTT, a.DLC, a.cop, a.be, a.at, 
       b.fyear as x_year, b.ia as l_ia, b.DLTT as l_DLTT, b.DLC as l_DLC, b.cop as l_cop, b.be as l_be, b.at as l_at
from myq5.compustat_funda as a left join myq5.compustat_funda as b
on a.gvkey = b.gvkey and a.fyear - 1 = b.fyear;
quit;
data myq5.compustat_funda2;
set myq5.compustat_funda2;
if fyr > 0;
if fyr <= 5 then cyear = fyear + 1;
else cyear = fyear;
year = year(datadate);
/*begin and end dates for fiscal year;*/
date1 = MDY(fyr,1,cyear);
endfyr = intnx('month',date1,0,'end'); format endfyr date9.;
begfyr = intnx('month',endfyr,-11,'beg'); format begfyr date9.;
run;
proc sort data=myq5.compustat_funda2; by gvkey fyear; run;
proc sql;
create table myq5.ccm
as select * from myq5.compustat_funda2 as a, mydisp.ccmxpf_linktable as b
where a.gvkey = b.gvkey
and b.LINKTYPE in ("LU","LC","LD","LF","LN","LS")
/*LO: links to trading company but no linking trading issues exists.*/
/*LX: links to foreign company with an issue trading on US exchanges.*/
and b.LINKPRIM in ("P", "C")
/*LO: links to trading company but no linking trading issues exists.*/
/*LX: links to foreign company with an issue trading on US exchanges.*/
/*Entire fiscal years within link date range*/
and (b.LINKDT<=a.endfyr or b.LINKDT = .B)
and (a.endfyr<=b.LINKENDDT or b.LINKENDDT=.E);
quit;
/* Cleaning Compustat Data for no relevant duplicates  
/* 1. Eliminating overlapping matching : few cases where different gvkeys   */
/*   for same permno-date --- some of them are not 'primary' matches in CCM.*/
/*   Use linkprim='P' for selecting just one gvkey-permno-date combination; */
proc sort data=myq5.ccm out=myq5.ccm2;
          by datadate lpermno descending linkprim gvkey;
run;
data myq5.ccm2a;
     set myq5.ccm2;
     by datadate lpermno descending linkprim gvkey;
     if first.lpermno;
run;
proc sort data=myq5.ccm2a nodupkey; by datadate lpermno; run;
/* 2. However, there other type of duplicates within the year                */
/* Some companies change fiscal year end in the middle of the calendar year. */
/* In these cases, there are more than one annual record for accounting data.*/
/* We will be selecting the last annual record in a given calendar year;     */
proc sort data=myq5.ccm2a; by lpermno fyear datadate ; run;
data myq5.ccm2b ;
     set myq5.ccm2a;
     by lpermno year datadate ;
     if last.year =1 ;
run;
proc sort data=myq5.ccm2b nodupkey; by lpermno datadate; run;
/* End cleaning Compustat        */
proc sql;
create table myq5.ccm2b as                 
select a.*, b.me as size
from myq5.ccm2b as a , mydisp.monthret_ff as b
where a.lpermno=b.permno
and year(a.datadate)=year(b.date) and month(a.datadate)=month(b.date)
order by b.date;
quit;
data myq5.ccm2b; set myq5.ccm2b;
tobinq=log(((size/1000)+dltt+dlc)/at);
run;
/*1-3. CRSP monthly return */
data myq5.monthret_ff(keep=permno date prc ret exret ME siccd exchcd shrcd cfacshr); 
     set mydisp.monthret_ff;
run;
proc printto log=junk; run;
proc expand data=myq5.monthret_ff out=myq5.monthret_ff method = none; 
  by permno;
  id date; 
  convert me = lme   / transformout=(lag 1); 
run;
proc printto; run;
data myq5.crsp;
     set myq5.monthret_ff;
     by permno;
     if first.permno then LME=me/(1+ret);
	 if (exchcd=1 or exchcd=2 or exchcd=3);
	 if (shrcd=10 or shrcd=11);
     if (siccd<6000 or siccd>6999);
run;
proc sort data = myq5.crsp;
    by date;
run;
proc univariate data=myq5.crsp noprint;
    var lme;
	by date;
	output out=myq5.tlmc sum=tlmc;
run;
data myq5.crsp2; merge myq5.tlmc myq5.crsp;
    by date;
	wi = lme/tlmc;
run;

/*2. Merge Monthly CRSP, Quarterly Compustat, and Annual Compustat */ 
/************************************************************************/
/*At the beginning of each month t, construct the expected 1-year-ahead                           */
/*investment-to-assets changes, E[dI/A], by combining the most recent                             */
/*winsorized predictors with the average slopes from the prior 120-month                         */
/*(30 months minimum).                                                                                                */
/*Predictors: logQ & Cop from the most recent fiscal year ending at                                 */
/*            least 4 months ago as of month t.                                                                     */
/*            dROE from the latest announced earnings. If not available,                                */
/*            from the most recent fiscal quarter ending at least 4                                           */
/*            months ago                                                                                                     */
/*Slopes: dI/A from the most recent fiscal year ending at least 4 months                            */
/*        ago as of month t.                                                                                                */
/*        Regressors further lagged by 12 months.                                                               */
/************************************************************************/

/*2-2.Merge data: CRSP + Annual Compustat*/ 
proc sql;
create table myq5.crsp_compustaty as                 
select a.*, b.date, b.lme, b.me, b.shrcd, b.exchcd, b.SICCD, b.wi
from myq5.ccm2b as a , myq5.crsp2 as b
where a.lpermno=b.permno
and intck('month',a.datadate,b.date) between 4 and 15
order by b.date;
quit;
/*2-3.Merge data: CRSP + Quarterly Compustat*/
proc sql;
create table myq5.ccqm2a1 as
select a.* , b.rdq as f_rdq
from myq5.compustat_fundqa4 as a left join myq5.compustat_fundqa4 as b
on a.lpermno = b.lpermno and a.fyq + 1 = b.fyq;
quit;
proc sql;
create table myq5.ccqm2a1_f2rdq as
select a.* , b.rdq as f2_rdq
from myq5.ccqm2a1 as a left join myq5.compustat_fundqa4 as b
on a.lpermno = b.lpermno and a.fyq + 2 = b.fyq;
quit;
proc sql;
create table myq5.ccqm2a1_f3rdq as
select a.* , b.rdq as f3_rdq
from myq5.ccqm2a1_f2rdq as a left join myq5.compustat_fundqa4 as b
on a.lpermno = b.lpermno and a.fyq + 3 = b.fyq;
quit;
proc sql;
create table myq5.ccqm2a1_f4rdq as
select a.* , b.rdq as f4_rdq
from myq5.ccqm2a1_f3rdq as a left join myq5.compustat_fundqa4 as b
on a.lpermno = b.lpermno and a.fyq + 4 = b.fyq;
quit;
proc sql;
create table myq5.ccqm2a1_f5rdq as
select a.* , b.rdq as f5_rdq
from myq5.ccqm2a1_f4rdq as a left join myq5.compustat_fundqa4 as b
on a.lpermno = b.lpermno and a.fyq + 5 = b.fyq;
quit;
proc sql;
create table myq5.ccqm2a1_f6rdq as
select a.* , b.rdq as f6_rdq
from myq5.ccqm2a1_f5rdq as a left join myq5.compustat_fundqa4 as b
on a.lpermno = b.lpermno and a.fyq + 6 = b.fyq;
quit;
proc sql;
create table myq5.ccqm2a2 as
select a.*, intck('month',a.rdq,a.f_rdq) as nummonth1, intck('month',a.rdq,a.f2_rdq) as nummonth2, intck('month',a.rdq,a.f3_rdq) as nummonth3, intck('month',a.rdq,a.f4_rdq) as nummonth4,
       intck('month',a.rdq,a.f5_rdq) as nummonth5, intck('month',a.rdq,a.f6_rdq) as nummonth6
from myq5.ccqm2a1_f6rdq as a;
quit;
data myq5.ccqm2a21 ;
     set myq5.ccqm2a2;
	 nummonth=min(6,coalesce(nummonth1,nummonth2,nummonth3,nummonth4,nummonth5,nummonth6));
run;
/*Exclude stale earnings and datadate is earlier than rdq*/
/*data myq5.ccqm2a2; set myq5.ccqm2a2; if maxmonth>6 and maxmonth ne . then delete; if maxmonth<0 and maxmonth ne . then delete; run;*/
proc sql;
create table myq5.crsp_compustatq1 as                 
select b.*, a.permno, a.date, a.lme, a.me, a.shrcd, a.exchcd, a.SICCD, a.wi, intck('month',b.datadate,a.date) as maxmonth,
intnx('month',a.date,0,'b') as portdate format date9. 
from myq5.crsp2 as a, myq5.ccqm2a21 as b
where a.permno=b.lpermno
and intck('month',b.rdq,a.date) between 0 and b.nummonth-1
and b.rdq ne .
and b.nummonth ne .
order by permno, date;
quit;
data myq5.crsp_compustatq2; set myq5.crsp_compustatq1; if maxmonth < 7; if maxmonth >= 0; yes=1; run;
proc sort data=myq5.crsp_compustatq2; by date; run;
/*Check when you use EIA factor before 1972*/
proc sql;
create table myq5.unmatched as
select a.*
from myq5.crsp2 as a left join myq5.crsp_compustatq1 as b
on a.permno=b.permno
and a.date=b.date
where b.permno is null
and   b.date is null
order by date;
quit;
proc sql;
create table myq5.crsp_compustatq3 as                 
select b.*, a.permno, a.date, a.lme, a.me, a.shrcd, a.exchcd, a.SICCD, a.wi,
intnx('month',a.date,0,'b') as portdate format date9.
from myq5.unmatched as a, myq5.ccqm2a21 as b
where a.permno=b.lpermno and year(a.date) <=1971
and intck('month',b.datadate,a.date) between 4 and 6
order by a.date, a.permno, b.datadate;
quit;
proc append base=myq5.crsp_compustatq2 data=myq5.crsp_compustatq3;
run;
proc sort data=myq5.crsp_compustatq2; by date permno yes; run;
proc sort data=myq5.crsp_compustatq2 nodupkey; by date permno; run;
/*2-4.Merge data: All*/
proc sql;
create table myq5.crsp_compustat as                 
select b.permno, a.gvkey, a.date, a.datadate, b.datadate as datadateq, a.fyear, b.fqtr, a.fyr, a.shrcd, a.exchcd, a.siccd, a.ME, a.lme, a.wi, 
       a.ia, a.d1_ia, a.cop, a.be, a.l_ia, a.l_cop, a.l_be, a.tobinq, 
       b.rdq, b.fyq, b.fyq_n, b.l_be as l_beq, b.roe, b.l4_roe, b.d_roe, b.l_droe
from myq5.crsp_compustaty as a , myq5.crsp_compustatq2 as b
where a.lpermno=b.permno
and a.date = b.date

order by date;
quit;
proc sql;
create table myq5.crsp_compustat1 as
select a.*, 
       b.tobinq as l_tobinq
from myq5.crsp_compustat as a left join myq5.crsp_compustat as b
on a.permno = b.permno and intnx('month',a.date,0,'E')=intnx('month',b.date,12,'E');
quit;
/*3. Winsorize all variables at 1 and 99% */ 
/*****************************************
Trim or winsorize macro
* byvar = none for no byvar;
* type  = delete/winsor (delete will trim, winsor will winsorize;
*dsetin = dataset to winsorize/trim;
*dsetout = dataset to output with winsorized/trimmed values;
*byvar = subsetting variables to winsorize/trim on;
****************************************/
  
%macro winsor(dsetin=, dsetout=, byvar=none, vars=, 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;
  
%if &byvar = none %then %do;
  
    data xtemp;
        set xtemp;
        xbyvar = 1;
        run;
  
    %let byvar = 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 not missing(trimvars{xi}) 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 not missing(trimvars{xi}) then do;
              if (trimvars{xi} < trimvarl{xi}) then delete;
              if (trimvars{xi} > trimvarh{xi}) then delete;
            end;
        %end;
  
    end;
    drop &varL &varH xbyvar xi;
    run;
  
%mend winsor;
/* invoke macro to winsorize */
%winsor(dsetin=myq5.crsp_compustat1, dsetout=myq5.crsp_compustat2, byvar=date, vars=d1_ia cop d_roe tobinq l_cop l_droe l_tobinq , type=winsor, pctl=1 99);
proc means data=myq5.crsp_compustat1;
var d1_ia cop d_roe tobinq l_cop l_droe l_tobinq; quit;
proc means data=myq5.crsp_compustat2;
var d1_ia cop d_roe tobinq l_cop l_droe l_tobinq; quit;

/*4.FM forecasting regression  */ 
/************************************************************************/
/*At the beginning of each month t, construct the expected 1-year-ahead */
/*investment-to-assets changes, E[dI/A], by combining the most recent   */
/*winsorized predictors with the average slopes from the prior 120-month*/
/*(30 months minimum).                                                  */
/*Predictors: logQ & Cop from the most recent fiscal year ending at     */
/*            least 4 months ago as of month t.                         */
/*            dROE from the latest announced earnings. If not available,*/
/*            from the most recent fiscal quarter ending at least 4     */
/*            months ago                                                */
/*Slopes: dI/A from the most recent fiscal year ending at least 4 months*/
/*        ago as of month t.                                            */
/*        Regressors further lagged by 12 months.                       */
/************************************************************************/
/*Start with no missing variables*/
data myq5.crsp_compustat3; set myq5.crsp_compustat2;
if d1_ia ne .; 
if cop ne .;
if tobinq ne .;
if d_roe ne .;
if l_cop ne .;
if l_tobinq ne .;
if l_droe ne .;
cym = intck("month",'1jan1960'd,date);
run;
/*Macro start*/
%macro FM_wls;
       %local i;
	   %do i = 95 %to 701;

       proc sql;
       create table myq5.rolling1 as
       select a.*
       from myq5.crsp_compustat3 as a
       where &i-119 <= a.cym <= &i;
       quit;

       proc reg data=myq5.rolling1 outest=myq5.fm_projection edf tableout noprint;
       by cym;
	   model d1_ia = l_tobinq l_cop l_droe;
	   weight me;
	   quit;

       data myq5.fm_projection2;
	   set myq5.fm_projection;
	   if _TYPE_ = 'PARMS';
	   keep _MODEL_ l_tobinq l_cop l_droe;
	   run;

	   proc means data=myq5.fm_projection2;
	   output out=myq5.fm_coeff mean=b_q b_cop b_droe;
	   run;

	   proc sql;
       create table myq5.rolling_&i as
       select a.*, b.*, (tobinq*b_q + cop*b_cop + d_roe*b_droe) as exp_ia, tobinq*b_q as tobinq_b, cop*b_cop as cop_b, d_roe*b_droe as droe_b
       from myq5.rolling1 as a, myq5.fm_coeff as b
       where a.cym=&i;
       quit;
       %end;
%mend FM_wls;
%FM_wls;
/*Append the rolling-window estimation*/
%macro all_fm;
       %local i;
	   %do i=95 %to 701;
	   proc append base=myq5.rolling_95 data=myq5.rolling_&i;
	   run;
	   %end;
%mend all_fm;
%all_fm;
proc corr data=myq5.rolling_95;
var d1_ia exp_ia b_q b_cop b_droe;
run;
/*Check the coefficients*/
proc univariate data=myq5.rolling_95 noprint;
     var b_q b_cop b_droe;
     by cym;
     output out=myq5.rolling_95_b mean=b_q b_cop b_droe;
run; 
data myq5.rolling_95_b;
set myq5.rolling_95_b;
if cym>=162;
run;
proc means data=myq5.rolling_95_b;
var b_q b_cop b_droe;
run;
/*****************************************************************/
/*From paper,   b_q=-0.031, b_cop=0.530, and b_droe=0.802                             */
/*From the code, b_q=-0.068, b_cop=0.813, and b_droe=0.885                           */
/*****************************************************************/
proc sql;
create table myq5.crsp_compustat4 as
select a.*, 
       b.d1_ia as f1_ia
from myq5.rolling_95 as a left join myq5.crsp_compustat3 as b 
on a.permno = b.permno and a.cym+1=b.cym;
quit;
proc corr data=myq5.crsp_compustat4 pearson spearman;
var exp_ia f1_ia d1_ia;
run;
proc sort data=myq5.crsp_compustat4;
by date;
run;
/*****************************************************************/
/*From paper,     Pearson correlation=0.14                                                         */
/*From the code, Pearson correlation=0.12                                                         */
/*****************************************************************/

/*5.Decile Portfolio */ 
proc univariate data=myq5.crsp_compustat4 noprint;
where exchcd=1 and shrcd in (10,11) and exchcd in (1,2,3);
var exp_ia;
by date; 
output out=myq5.nyse_eia  pctlpts=10 20 30 40 50 60 70 80 90 pctlpre=Eia;
run;
/* Use Breakpoints to classify stock only at end of all June's */
proc sql;
create table myq5.Eia10 as
select a.permno, a.date, a.me, a.lme, a.wi, a.d1_ia, a.cym, a.exp_ia, a.f1_ia,
       b.eia10, b.eia20, b.eia30, b.eia40, b.eia50, b.eia60, b.eia70, b.eia80, b.eia90
from myq5.crsp_compustat4 as a, myq5.nyse_eia as b
where a.date=b.date;
quit;
data myq5.PF_decile;
     set myq5.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 exp_ia EIAport nonmissport;
run;
proc sql;
create table myq5.PF_return10 as
select a.date, a.permno, a.ret, a.wi, b.date as portdate format date9., b.EIAport, b.nonmissport
from myq5.crsp2 as a, myq5.PF_decile as b
where a.permno=b.permno 
and intnx('month',a.date,0,'E')=
    intnx('month',b.date,1,'E')
order by date , EIAport;
quit;
proc univariate noprint data=myq5.PF_return10;
    var ret;
    by date EIAport;
    weight wi;
    output out = myq5.portret10 mean = vwret n = N;
run;
proc means data=myq5.portret10 (keep=EIAport n vwret );
class EIAport;
run;
proc transpose data=myq5.portret10(keep=date EIAport vwret) out=myq5.portret10t (drop=_name_ _label_);
by date;
ID EIAport;
Var vwret;
run;
proc sql;
     create table myq5.portret10t as
	 select a.*,
            b.q_me, b.q_IA, b.q_roe, b.mktrf
	 from myq5.portret10t as a, myq.factors as b
     where a.date=b.date;
quit;
proc model data = myq5.portret10t;
parms b0 b1 b2 b3 b4 ; 
      _10 = b0+b1*mktrf+b2*q_me+b3*q_IA+b4*q_roe;
fit _10 /gmm kernel = (bart,7,0) vardef=n; 
instruments  mktrf q_me q_IA q_roe;
test mktrf, q_me, q_IA, q_roe;
run;
/**************************************************************************************/
/*                      1       2      3      4      5     6      7      8      9     10          10-1                                            */
/*From paper, -.12     .26   .33    .45   .46   .51   .57   .65   .75   .95         1.06                                             */
/*From the code, -.0005  .33   .42    .29   .45   .47   .52   .72   .66   .92          .921                                             */
/**************************************************************************************/

/*6.EIA factor construction */ 
proc univariate data=myq5.crsp_compustat4 noprint;
where exchcd=1 and shrcd in (10,11) and exchcd in (1,2,3);
var exp_ia;
by date; 
output out=myq5.factorbp_eia  pctlpts=30 70 pctlpre=Eia;
run;
proc univariate data=myq5.crsp_compustat4 noprint;
where exchcd=1 and shrcd in (10,11) and exchcd in (1,2,3);
var me;
by date; 
output out=myq5.nyse_me  pctlpts=50 pctlpre=ME;
run;
/* Use Breakpoints to classify stock only at end of all June's */
proc sql;
create table myq5.Eia_factor as
select a.*, b.Eia30, b.Eia70, c.me50
from myq5.crsp_compustat4 as a, myq5.factorbp_eia as b, myq5.nyse_me as c
where a.date=b.date=c.date;
quit;
data myq5.PF_sorting;
     set myq5.Eia_factor;
     if ME <= ME50                                    then SIZEport = 'S';
     else if ME > ME50                                then SIZEport = 'B';
     if exp_ia <= Eia30                               then EIAport  = 'L';
     else if exp_ia >  Eia30 and exp_ia <= Eia70      then EIAport  = 'M';
     else if exp_ia  > Eia70                          then EIAport  = 'H';
if missing(sizeport)=0 and missing(EIAport)=0 then nonmissport=1;
else nonmissport=0;
keep permno date SIZEport EIAport nonmissport;
run;
proc sql;
create table myq5.PF_return as
select a.date, a.permno, a.exret, a.ret, a.wi, b.date as portdate format date9., b.SIZEport, b.EIAport, b.nonmissport
from myq5.crsp2 as a, myq5.PF_sorting as b
where a.permno=b.permno 
and intnx('month',a.date,0,'E')=
    intnx('month',b.date,1,'E')
and a.exret ne .
order by date , SIZEport, EIAport;
quit;
proc univariate noprint data=myq5.PF_return;
    var ret;
    by date SIZEport EIAport;
    weight wi;
    output out = myq5.portret mean = vwret n = N;
run;
proc transpose data=myq5.portret(keep=date SIZEport EIAport vwret) out=myq5.portret2 (drop=_name_ _label_);
by date;
ID SIZEport EIAport;
Var vwret;
run;
data myq5.EIA_factor(keep=date High Low EIA);
     set myq5.portret2;
	 High=(BH+SH)/2;
	 Low=(BL+SL)/2;
	 EIA=High - Low;
run;
proc means data=myq5.EIA_factor;
run;
proc sql;
     create table myq5.EIA_factor as
	 select a.*,
            b.q_me, b.q_IA, b.q_roe, b.mktrf
	 from myq5.EIA_factor as a, myq.factors as b
     where a.date=b.date;
quit;
proc model data = myq5.EIA_factor;
parms b0 b1 b2 b3 b4 ; 
      EIA = b0+b1*mktrf+b2*q_me+b3*q_IA+b4*q_roe;
fit EIA /gmm kernel = (bart,7,0) vardef=n; 
instruments  mktrf q_me q_IA q_roe;
test mktrf, q_me, q_IA, q_roe;
run;
proc corr data=myq5.EIA_factor;
var mktrf q_me q_ia q_roe EIA;
run;
data myq5.EIA_factor;
     set myq5.EIA_factor;
	 year=year(date);
	 month=month(date);
	 var1=year*100+month;
run;
/******************************************************************************/
/*                    EIA factor     q_alpha     q_me     q_IA     q_roe     R^2                                        */
/*From paper,      .82               .63          -.09        .25       .30        .48                                         */
/*From the code,       .7856           .56          -.06        .47       .47        .41                                          */
/******************************************************************************/
/******************************************************/
/*correlation   mktrf     q_me     q_IA     q_roe                               */
/*From paper,   -.47      -.37      .38       .52                                    */
/*From the code,    -.29      -.22      .17       .61                                    */
/******************************************************/

/*7.Annual EIA factor*/ 
proc printto log = junk; run;
proc expand data = myq5.portret2 out=myq5.portret_y method=none;
    id date;
    convert bh = bhy    / 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 sh = shy     / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sl = sly      / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);
	convert sm = smy  / transformin=(+1) transformout=(MOVPROD  12 -1 trimleft  11);    
quit;
proc printto; run;
data myq5.EIA_factory(keep=date High Low EIA);
     set myq5.portret_y;
	 High=(BHy+SHy)/2;
	 Low=(BLy+SLy)/2;
	 EIA=High - Low;
run;
proc means data=myq5.EIA_factory;
run;
data myq5.EIA_factory;
     set myq5.EIA_factory;
	 year=year(date);
	 month=month(date);
	 var1=year*100+month;
run;


