/********************************************************************* */
/*  Program        : 0.Constructannualtestassets45.sas                       */
/*  Description    : Construct the 45 annual test assets used in the    */
/*                   annual Fama-MacBeth regressions (Tables 2-3):      */
/*                   6 size-BM, 6 size-Inv, 6 size-OP, 6 size-Mom,        */
/*                   5 Fama-French industry, 6 size-EG, and 10             */
/*                   operating accrual portfolios.                                    */
/*                                                                                                 */
/*  Input : data\   6sizebm.xlsx, 6sizeinv.xlsx, 6sizeop.xlsx,          */
/*          6sizemom.xlsx, 5ind.xlsx (Kenneth French library),         */
/*          portf_oa_monthly_2019a.csv (q-data library)                  */
/*          output\q5\ portret_y (annualized size-EG portfolios         */
/*          bhy-smy, from 0.Q5factorconstruction.sas)                      */
/*  Output: Table2\ testasset45y_negativeprc.csv (45 assets)            */
/********************************************************************* */

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

/*--------------------------------------------------------------------*/
/* 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=);
PROC IMPORT datafile="&file" OUT=work.&ds DBMS=xlsx 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 2x3 size-sorted xlsx whose columns are named      */
/* sl, sm, sh, bl, bm, bh (in percent) -> p1-p6                        */
/*--------------------------------------------------------------------*/
%macro getxlsx6(file=, ds=);
PROC IMPORT datafile="&file" OUT=work.&ds DBMS=xlsx REPLACE; GETNAMES=Yes; RUN;
proc sql;
create table work.&ds as
select a.var1, sl/100 as p1, sm/100 as p2, sh/100 as p3,
       bl/100 as p4, bm/100 as p5, bh/100 as p6
from work.&ds as a
where 197201 <= var1 <= 201806;
quit;
%mend getxlsx6;

/*--------------------------------------------------------------------*/
/* Macro 4: 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;

/*====================================================================*/
/* The 45 annual test assets                                           */
/*====================================================================*/
%getxlsx6(file=&projpath.\data\6sizebm.xlsx,  ds=bm6);  %annualize(ds=bm6,  n=6, pre=bm);
%getxlsx6(file=&projpath.\data\6sizeinv.xlsx, ds=inv6); %annualize(ds=inv6, n=6, pre=inv);
%getxlsx6(file=&projpath.\data\6sizeop.xlsx,  ds=op6);  %annualize(ds=op6,  n=6, pre=op);
%getxlsx6(file=&projpath.\data\6sizemom.xlsx, ds=mom6); %annualize(ds=mom6, n=6, pre=mom);
%getxlsx(file=&projpath.\data\5ind.xlsx,      ds=ind5, n=5); %annualize(ds=ind5, n=5, pre=ind);
%getqlib(file=&projpath.\data\portf_oa_monthly_2019a.csv, ds=oa10, sig=OA, n=10);
%annualize(ds=oa10, n=10, pre=p);
/*Size-EG portfolios: the annualized returns (bhy-smy) from 0.Q5factorconstruction.sas*/
data work.eg6_y; set myq5.portret_y;
var1=year(date)*100+month(date);
If month(date)=6;
If 197306 <= var1 <= 201706;
keep var1 bhy bly bmy shy sly smy;
run;

/*Merge in the order of the columns of testasset45y_negativeprc.csv*/
proc sql;
create table myt2.testasset45y as
select a.*, b.*, c.*, d.*, e.*, f.*, g.*
from work.bm6_y as a, work.inv6_y as b, work.op6_y as c, work.mom6_y as d,
     work.ind5_y as e, work.eg6_y as f, work.oa10_y as g
where a.var1=b.var1=c.var1=d.var1=e.var1=f.var1=g.var1;
quit;
proc export data=myt2.testasset45y outfile="&projpath.\Table2\testasset45y_negativeprc.csv" dbms=csv replace;
run;
