/********************************************************************* */
/*  Program        : 0.Constructannualtestassets198.sas                     */
/*  Description    : Construct the 198 annual test assets used in the   */
/*                   Giglio-Xiu (2021) three-pass regressions                  */
/*                   (Table 5 and Appendix Table H4):                            */
/*                   (1) 25 operating profitability x investment                 */
/*                   portfolios (Kenneth French library);                         */
/*                   (2) 10 book-to-market, 10 expected investment         */
/*                   growth, 10 discretionary accruals,                             */
/*                   10 organizational capital, 10 operating cash               */
/*                   flow, 10 changes in ROE, and 10 sales-to-price         */
/*                   portfolios (q-data library);                                          */
/*                   (3) 10 coskewness, 10 inventory growth, 10              */
/*                   cash-based operating profitability, 10 intangible         */
/*                   return, 10 past trading volume, 5 Dimson beta,          */
/*                   5 quarterly cashflow-to-market, 5 changes in             */
/*                   financial liabilities, 5 changes in net operating           */
/*                   assets, 5 net external financing, 5 zero trades,           */
/*                   5 firm age, 5 book leverage, and 3 one-year share     */
/*                   issuance portfolios (Open Source Asset Pricing;        */
/*                   the signal acronyms follow SignalDoc.csv);              */
/*                   (4) 10 abnormal return portfolios, following              */
/*                   Chan et al. (1996) (constructed by the authors).        */
/*                                                                                                 */
/*  Input : Table5\testassets\ opinv25.xlsx,                                    */
/*          portf_*_monthly_*.csv (q-data library),                           */
/*          PredictorAltPorts_DecilesVW\PredictorAltPorts_DecilesVW.csv */
/*          (Open Source Asset Pricing), abr10.csv (provided by the             */
/*            author)                                                                                      */
/*  Output: Table5\ testasset198.csv                                                           */
/*  Note  : Download monthly portfolio files first:                                     */
/*  25_Portfolios_OP_INV (opinv25.xlsx) from the Kenneth French         */
/*   library; portf_*_monthly_2023.csv from the  q-data library                 */
/*  ; PredictorAltPorts_DecilesVW.csv,                                                     */
/*  PredictorAltPorts_QuintilesVW.csv, PlaceboPortsFull.csv, and              */
/*  SignalDoc.csv from Open Source Asset Pricing                                   */
/*  (openassetpricing.com). abr10.csv is provided in Table5\testassets.       */                              
/********************************************************************* */

%let projpath = D:\SAS\TFP\Dispersion_replication;
libname myt5 "&projpath.\Table5";
libname mygx "&projpath.\Table5\testassets";

/*--------------------------------------------------------------------*/
/* Macro 1: annualize work.&ds (var1, p1-p&n in decimals) ->           */
/* work.&ds._y with the June observations of the 12-month compounded   */
/* returns (&pre.1y - &pre.&n.y).                                      */
/*--------------------------------------------------------------------*/
%macro annualize(ds=, n=, pre=p);
proc printto log = junk; run;
proc expand data = work.&ds out=work.&ds._y method=none;
    id var1;
    %do i=1 %to &n;
    convert p&i = &pre.&i.y / transformin=(+1) transformout=(MOVPROD 12 -1 trimleft 11);
    %end;
quit;
proc printto; run;
data work.&ds._y; set work.&ds._y;
Year = int(Var1/100);
Month = Var1-Year*100;
If Month=6;
/*The testing period: June 1973 - June 2017 (45 annual observations)*/
If 197306 <= Var1 <= 201706;
keep var1 %do i=1 %to &n; &pre.&i.y %end;;
run;
%mend annualize;

/*--------------------------------------------------------------------*/
/* Macro 2: import an xlsx with columns var1, p1-p&n (in percent)      */
/*--------------------------------------------------------------------*/
%macro getxlsx(file=, ds=, n=, dbms=xlsx);
PROC IMPORT datafile="&file" OUT=work.&ds DBMS=&dbms REPLACE; GETNAMES=Yes; RUN;
proc sql;
create table work.&ds as
select a.var1 %do i=1 %to &n; , p&i/100 as p&i %end;
from work.&ds as a
where 197201 <= var1 <= 201806;
quit;
%mend getxlsx;

/*--------------------------------------------------------------------*/
/* Macro 3: import a q-data library monthly portfolio csv (long        */
/* format: year, month, rank_&sig, ret_vw in percent)                  */
/*--------------------------------------------------------------------*/
%macro getqlib(file=, ds=, sig=, n=);
PROC IMPORT datafile="&file" OUT=work.&ds DBMS=csv REPLACE; GETNAMES=Yes; RUN;
data work.&ds; set work.&ds; var1=year*100+month; run;
proc sort data=work.&ds; by var1; run;
proc transpose data=work.&ds out=work.&ds;
by var1; ID rank_&sig; var ret_vw;
run;
proc sql;
create table work.&ds as
select a.var1 %do i=1 %to &n; , _&i/100 as p&i %end;
from work.&ds as a
where 197201 <= var1 <= 201806;
quit;
%mend getqlib;

/*--------------------------------------------------------------------*/
/* Macro 4: extract one signal from the Open Source Asset Pricing      */
/* deciles file (long format: signalname, port, date, ret in percent). */
/* The acronyms follow SignalDoc.csv. &n is the number of portfolios   */
/* available for the signal (10, 5, or 3).                             */
/*--------------------------------------------------------------------*/
%macro getosap(sig=, ds=, n=, src=osap);
data work.&ds; set work.&src;
where signalname="&sig";
var1=year(date)*100+month(date);
run;
proc sort data=work.&ds; by var1 port; run;
proc transpose data=work.&ds out=work.&ds prefix=q;
by var1; ID port; var ret;
run;
/*The port values are zero-padded (01, 02, ...), so the transposed*/
/*variables are q01-q10.*/
proc sql;
create table work.&ds as
select a.var1 %do i=1 %to &n; , q%sysfunc(putn(&i,z2.))/100 as p&i %end;
from work.&ds as a
where 197201 <= var1 <= 201806;
quit;
%mend getosap;

/*====================================================================*/
/* 1. The abnormal return portfolios (Chan et al. 1996)                */
/*====================================================================*/
/*abr10.csv contains the annualized June returns (var1 and p1y-p10y,*/
/*already in decimals), so it is imported directly without the*/
/*annualization step.*/
PROC IMPORT datafile="&projpath.\Table5\testassets\abr10.csv"
    OUT=work.abr_y DBMS=csv REPLACE; GETNAMES=Yes;
RUN;
proc sql;
create table work.abr_y as
select var1, p1y as abr1y, p2y as abr2y, p3y as abr3y, p4y as abr4y, p5y as abr5y,
       p6y as abr6y, p7y as abr7y, p8y as abr8y, p9y as abr9y, p10y as abr10y
from work.abr_y
where 197306 <= var1 <= 201706;
quit;

/*====================================================================*/
/* 2. The 25 operating profitability x investment portfolios (French)  */
/*====================================================================*/
%getxlsx(file=&projpath.\Table5\testassets\opinv25.xlsx, ds=opinv, n=25);
%annualize(ds=opinv, n=25, pre=opinv);

/*====================================================================*/
/* 3. The q-data library decile portfolios                             */
/*====================================================================*/
%getqlib(file=&projpath.\Table5\testassets\portf_eg_1_monthly_2023.csv,   ds=eg_1,  sig=EG_1,   n=10); %annualize(ds=eg_1,  n=10, pre=eg);
%getqlib(file=&projpath.\Table5\testassets\portf_dac_monthly_2023.csv,  ds=dac,  sig=DAC,  n=10); %annualize(ds=dac,  n=10, pre=dac);
%getqlib(file=&projpath.\Table5\testassets\portf_oca_monthly_2023.csv,  ds=oca,  sig=OCA,  n=10); %annualize(ds=oca,  n=10, pre=oca);
%getqlib(file=&projpath.\Table5\testassets\portf_cop_monthly_2023.csv,  ds=cop,  sig=COP,  n=10); %annualize(ds=cop,  n=10, pre=cop);
%getqlib(file=&projpath.\Table5\testassets\portf_droe_1_monthly_2023.csv, ds=droe_1, sig=DROE_1, n=10); %annualize(ds=droe_1, n=10, pre=droe);
%getqlib(file=&projpath.\Table5\testassets\portf_bm_monthly_2023.csv,   ds=bm, sig=BM,   n=10); %annualize(ds=bm, n=10, pre=bmd);
%getqlib(file=&projpath.\Table5\testassets\portf_sp_monthly_2023.csv,   ds=sp,   sig=SP,   n=10); %annualize(ds=sp,   n=10, pre=sp);

/*====================================================================*/
/* 4. The Open Source Asset Pricing portfolios                         */
/* (the acronyms follow SignalDoc.csv)                                 */
/*====================================================================*/
/*Read the csv with a data step: proc import sets the length of signalname*/
/*from the first values (e.g., "AM") and truncates the longer acronyms.*/
/*port is read as a character ("01", ...), so the transposed variables*/
/*are q01-q10.*/
data work.osap;
infile "&projpath.\Table5\testassets\PredictorAltPorts_DecilesVW.csv"
    dsd firstobs=2 truncover;
length signalname $40 port $2;
informat date yymmdd10.;
format date yymmdd10.;
input signalname $ port $ date ret signallag Nlong Nshort;
run;
%getosap(sig=Coskewness,  ds=cosk, n=10); %annualize(ds=cosk, n=10, pre=cosk);
%getosap(sig=InvGrowth,   ds=ivg,  n=10); %annualize(ds=ivg,  n=10, pre=ivg);
%getosap(sig=CBOperProf,  ds=cbop, n=10); %annualize(ds=cbop, n=10, pre=cbop);
%getosap(sig=IntanBM,     ds=intbm,n=10); %annualize(ds=intbm,n=10, pre=intbm);
%getosap(sig=DolVol,      ds=dvol, n=10); %annualize(ds=dvol, n=10, pre=dvol);

/*The quintile and tercile portfolios are taken from the quintiles file.*/
/*The csv is read with a data step so that the length of signalname is*/
/*not set by its first values; port is read as a character ("01", ...),*/
/*so the transposed variables are q01-q05 as above.*/
data work.osapq;
infile "&projpath.\Table5\testassets\PredictorAltPorts_QuintilesVW.csv"
    dsd firstobs=2 truncover;
length signalname $40 port $2;
informat date yymmdd10.;
format date yymmdd10.;
input signalname $ port $ date ret signallag Nlong Nshort;
run;
%getosap(sig=ShareIss1Y,  ds=si1y, n=3, src=osapq);  %annualize(ds=si1y, n=3,  pre=si);
%getosap(sig=DelFINL,     ds=dfnl, n=5, src=osapq);  %annualize(ds=dfnl, n=5,  pre=dfnl);
%getosap(sig=dNoa,        ds=dnoa, n=5, src=osapq);  %annualize(ds=dnoa, n=5,  pre=dnoa);
%getosap(sig=XFIN,        ds=xfin, n=5, src=osapq);  %annualize(ds=xfin, n=5,  pre=xfin);
%getosap(sig=zerotrade6M, ds=ztrd, n=5, src=osapq);  %annualize(ds=ztrd, n=5,  pre=ztrd);
%getosap(sig=FirmAge,     ds=fage, n=5, src=osapq);  %annualize(ds=fage, n=5,  pre=fage);
%getosap(sig=BookLeverage,ds=blev, n=5, src=osapq);  %annualize(ds=blev, n=5,  pre=blev);

/*BetaDimson and CFq are classified as placebos in SignalDoc.csv, so they*/
/*are not in the predictor files; they are taken from the placebo*/
/*portfolio file (ports 01-05; the LS rows are not used).*/
data work.osapf;
infile "&projpath.\Table5\testassets\PlaceboPortsFull.csv"
    dsd firstobs=2 truncover;
length signalname $40 port $2;
informat date yymmdd10.;
format date yymmdd10.;
input signalname $ port $ date ret signallag Nlong Nshort;
run;
%getosap(sig=BetaDimson,  ds=bdim, n=5, src=osapf);  %annualize(ds=bdim, n=5,  pre=bdim);
%getosap(sig=CFq,         ds=cfq,  n=5, src=osapf);  %annualize(ds=cfq,  n=5,  pre=cfq);

/*====================================================================*/
/* 5. Diagnostic: the first June observation of each family.           */
/* Every family must start at 197306; a later start truncates the      */
/* merged file (replace the input with an earlier vintage).            */
/*====================================================================*/
%macro firstobs(ds);
proc sql; select "&ds" as family, min(var1) as first_june, count(*) as n_years
from work.&ds._y; quit;
%mend firstobs;
%firstobs(abr);  %firstobs(opinv); %firstobs(cosk); %firstobs(eg1);  %firstobs(dac);
%firstobs(oca);  %firstobs(cop);   %firstobs(ivg);  %firstobs(cbop); %firstobs(intbm);
%firstobs(dvol); %firstobs(si1y);  %firstobs(bdim); %firstobs(cfq);  %firstobs(dfnl);
%firstobs(dnoa); %firstobs(xfin);  %firstobs(ztrd); %firstobs(droe); %firstobs(bm10);
%firstobs(sp);   %firstobs(fage);  %firstobs(blev);

/*====================================================================*/
/* 6. Merge in the order of the columns of testasset198.csv            */
/*====================================================================*/
proc sql;
create table myt5.testasset198 as
select a.*, b.*, c.*, d.*, e.*, f.*, g.*, h.*, i.*, j.*, k.*, l.*, m.*, n.*, o.*, p.*, q.*, r.*, s.*, t.*, u.*, v.*, w.*
from work.abr_y as a, work.opinv_y as b, work.cosk_y as c, work.eg_1_y as d,
     work.dac_y as e, work.oca_y as f, work.cop_y as g, work.ivg_y as h,
     work.cbop_y as i, work.intbm_y as j, work.dvol_y as k, work.si1y_y as l,
     work.bdim_y as m, work.cfq_y as n, work.dfnl_y as o, work.dnoa_y as p,
     work.xfin_y as q, work.ztrd_y as r, work.droe_1_y as s, work.bm_y as t,
     work.sp_y as u, work.fage_y as v, work.blev_y as w
where a.var1=b.var1=c.var1=d.var1=e.var1=f.var1=g.var1=h.var1=i.var1=j.var1
  =k.var1=l.var1=m.var1=n.var1=o.var1=p.var1=q.var1=r.var1=s.var1=t.var1
  =u.var1=v.var1=w.var1;
quit;
proc export data=myt5.testasset198 outfile="&projpath.\Table5\testasset198.csv" dbms=csv replace;
run;
