/* 
This SAS code downloads the necessary data for TFP estimation from CRSP and
Compustat (WRDS), following Tuzel and Imrohoroglu (2014).

Outputs (saved under &projpath.\data):
  - monthlyret / monthret_ff : CRSP monthly returns merged with FF factors
  - compustat_name.dta       : Compustat names file (gvkey, sic, naics, year1, year2)
  - compustat_annual.dta     : Compustat annual data merged with CCM link table and
                               CRSP price

Requirements:
  - A WRDS account (signon prompts for username/password).
    The CCM link table (ccmxpf_linktable) is downloaded from WRDS below.
  - FF5.csv and nysebp.csv, downloaded from Kenneth French's data library,
    must exist in &projpath.\data.
*/

/* ---------------------------------------------------------------
   Set the paths below before running.
   --------------------------------------------------------------- */
%let projpath = D:\SAS\TFP\Dispersion_replication;

%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=TCP remote=WRDS;
signon username=_prompt_;

libname mydisp "&projpath.\data";

rsubmit;
libname crspm '/wrds/crsp/sasdata/m_stock';
%let final_ds = msf2a;
%let begdate = '01JAN1963'd;
*  %let begdate = %str();
%let enddate = '31DEC2018'd;
* %let enddate = %str(); 

options nosource;
/* --------------------------------------------------------------- */
/* Select the variables to include in the output.                  */
/*   MSFVARS refers to variables in the Monthly Stock File (MSF)     */
/*   MSEVARS refers to variables in the Events file (MSEALL)       */
/* If no Event variables are needed, set MSEVARS to a null string. */
/* Note that PERMNO and DATE are included in the output by default */
/* --------------------------------------------------------------- */
options source;

 %let msfvars = prc ret vol shrout cfacshr retx ;

 %let msevars = ticker ncusip comnam exchcd shrcd siccd hsiccd permco permno dlret dlstcd;
* %let msevars = %str();

options nosource;
/* --------------------------------------------------------------------- */
/* Select the securities to include in the sample. For example:          */
/*     %let secid = and ticker in ("IBM","MSFT","DELL");                 */
/*     %let secid = and permno in (12490, 11081, 10107);                 */
/*     %let secid = and ncusip in ("45920010","59491810","24702R10");    */
/* To include all securities, set SECID to a null string.                */
/* PLEASE NOTE: If no event variables are selected for output (msevars   */
/* is null) and securities are being specified (SECID is not null), then */
/* SECID must condition on a variable in the MSF file (e.g., PERMNO or   */
/* PERMCO). For example, if msevars is null then SECID might select      */
/* stocks from the MSF file by PERMNO. But if SECID selects stocks based */
/* on TICKER, then TICKER or another MSEALL variable should be included  */
/* in the msevars list above.                                            */
/* --------------------------------------------------------------------- */
options source;

*  %let secid = and ticker in ("IBM","MSFT","DELL");
%let secid = %str();

options nosource;
/* ---------------------------------------------------------- */
/* Option to select specific share codes or exchange codes.   */
/* ---------------------------------------------------------- */

*   %let exchangecode =  ;
%let exchangecode = %str();

*    %let sharecode = and shrcd in (10,11);
%let sharecode = %str();
options source;

/* ------------------- */
/* END USER INPUT AREA */
/* ------------------- */

%macro getdata;

  options nodate nocenter nonumber ps=max ls=max symbolgen macrogen mlogic mprint;
  options nosource;
  /* ---------------------------------------------------------------- */
  /* If no MSEALL variables are needed (msevars is null) or if all    */
  /* securities are selected for output (SECID is null), extract data */
  /* directly from the MSF file. Remember that if no event variables  */
  /* are required but SECID is not null, SECID must condition on a    */
  /* variable in the MSF file (e.g., PERMNO).                         */
  /* ---------------------------------------------------------------- */
  options source;

  %if (%length(&msevars) = 0) or (%length(&secid) = 0) %then %do;

    proc sort data=crsp.msf out=&final_ds (keep=permno date &msfvars);
      where 1 &secid and date between &begdate and &enddate;
      by permno date;
    run;

  %end;

  options nosource;
  /* ----------------------------------------------------- */
  /* If MSEALL variables are selected for output (msevars  */
  /* is not null), extract data from the MSEALL file.      */
  /* ----------------------------------------------------- */
  options source;

  %if %length(&msevars) > 0 %then %do;

    proc sort data=crsp.mseall out=msedata (keep=permno date &msevars);
      where 1 &secid and date <= &enddate;
      by permno date;
    run;

    options nosource;
    /* -------------------------------------------------------- */
    /* If the sample does not include all securities (SECID is  */
    /* not null), get the unique PERMNOs for the sample, select */
    /* MSF data for those PERMNOs, and then sort the data.      */
    /* -------------------------------------------------------- */
    options source;

    %if %length(&secid) > 0 %then %do;

      proc sort data=msedata out=perms (keep=permno) nodupkey;
        by permno;
      run;

      proc sql;
        create table &final_ds (keep=permno date &msfvars) as
        select msf.*
        from perms, crsp.msf
        where perms.permno = MSF.permno and (date between &begdate and &enddate);
      quit;

     /* (PROC SQL does not necessarily preserve sort order.) */
      proc sort data=&final_ds;
        by permno date;
      run;

    %end;

    /* ------------------------------------------------------- */
    /* Merge the MSF and MSEALL data; copy information from    */
    /* event observations to succeeding non-event observations */
    /* ------------------------------------------------------- */
    data &final_ds (keep=permno date &msfvars &msevars);
    merge msedata (in=eventdata) &final_ds (in=stockdata);
    by permno date;
    retain lticker lcomnam lncusip lshrout lsiccd lexchcd lshrcls 
           lshrcd lshrflg ltrtscd lnmsind lmmcnt lnsdinx;
    varstring = "&msevars";
    if eventdata or first.permno then do;
      if index(varstring,"ticker") then lticker = ticker;
      if index(varstring,"comnam") then lcomnam = comnam;
      if index(varstring,"ncusip") then lncusip = ncusip;
      if index(varstring,"shrout") then lshrout = shrout;
      if index(varstring,"siccd")  then lsiccd  = siccd;
      if index(varstring,"exchcd") then lexchcd = exchcd;
      if index(varstring,"shrcls") then lshrcls = shrcls;
      if index(varstring,"shrcd")  then lshrcd  = shrcd;
      if index(varstring,"shrflg") then lshrflg = shrflg;
      if index(varstring,"trtscd") then ltrtscd = trtscd;
      if index(varstring,"nmsind") then lnmsind = nmsind;
      if index(varstring,"mmcnt")  then lmmcnt  = mmcnt;
      if index(varstring,"nsdinx") then lnsdinx = nsdinx;
    end;
    if not eventdata then do;
      if index(varstring,"ticker") then ticker = lticker;
      if index(varstring,"comnam") then comnam = lcomnam;
      if index(varstring,"ncusip") then ncusip = lncusip;
      if index(varstring,"shrout") then shrout = lshrout;
      if index(varstring,"siccd")  then siccd  = lsiccd;
      if index(varstring,"exchcd") then exchcd = lexchcd;
      if index(varstring,"shrcls") then shrcls = lshrcls;
      if index(varstring,"shrcd")  then shrcd  = lshrcd;
      if index(varstring,"shrflg") then shrflg = lshrflg;
      if index(varstring,"trtscd") then trtscd = ltrtscd;
      if index(varstring,"nmsind") then nmsind = lnmsind;
      if index(varstring,"mmcnt")  then mmcnt  = lmmcnt;
      if index(varstring,"nsdinx") then nsdinx = lnsdinx;
    end;
    if eventdata and not stockdata then delete;
    drop lticker lcomnam lncusip lshrout lsiccd lexchcd lshrcls
         lshrcd lshrflg ltrtscd lnmsind lmmcnt lnsdinx varstring;
    run;

    options nosource;
    /* ------------------------------------------------------------------------------ */
    /* The following sort is included to handle duplicate observations when a company */
    /* has more than one distribution on a given date. For example, a stock and cash  */
    /* distribution on the same date will generate two records, identical except for  */
    /* different DISTCD and DISTAMT (and possibly other) values. The NODUPLICATES     */
    /* option only deletes a record if all values for all variables are the same as   */
    /* those in another record. So, in the above example, if DISTCD is included in    */
    /* &msevars a record will not be deleted, but a redundant record will be deleted  */
    /* if DISTCD and DISTAMT are not included in &msevars.                            */
    /* ------------------------------------------------------------------------------ */
    options source;

    proc sort data=&final_ds noduplicates;
	  where 1 &exchangecode &sharecode;
      by permno date;
    run;

  %end;

%mend getdata;

%getdata;
proc download data=&final_ds out=monthlyret;
run;
endrsubmit;
proc sort data=mydisp.monthlyret; by date permco; run;
proc sql;
create table mydisp.monthlyret
as select a.*,
  abs(a.prc*a.shrout) as ME  'Market Value of Equity'
from mydisp.monthlyret as a ;
quit;
 
proc sort data=mydisp.monthlyret; by date permco ME; run;
/* There should be no duplicates*/
proc sort data=mydisp.monthlyret nodupkey; by permno date;run;
/*Copy monthlyret to your library*/
rsubmit;
libname ff '/wrds/ff/sasdata';
%let begdate = '01JAN1963'd;
*  %let begdate = %str();
%let enddate = '31DEC2018'd;
*  %let enddate = %str(); 
proc sql;
     create table factors as
     select factors_monthly.*
     from ff.factors_monthly
     where &begdate <= date <= &enddate;
quit;
proc sort data=factors out=factor;
          by date;
run;
proc download data=factor out=mydisp.factor;
run;
endrsubmit;
signoff;

/*Merge with FF 5 factors*/
PROC IMPORT datafile="&projpath.\data\FF5.csv"
 	OUT=mydisp.FF5
 	DBMS=csv
 	REPLACE;
 	GETNAMES=Yes;
RUN;
proc sql;
     create table mydisp.FF5(drop= Mkt_RF SMB HML RMW CMA RF) as
     select a.*, Mkt_RF/100 as mktrf, SMB/100 as SMB1, HML/100 as HML1, RMW/100 as RMW1, CMA/100 as CMA1, RF/100 as RF1
     from mydisp.FF5 as a
     where 196301 <= var1 <= 201812;
quit;
data mydisp.FF5(rename=(SMB1=SMB)rename=(HML1=HML)rename=(RMW1=RMW)rename=(CMA1=CMA)rename=(RF1=RF));
     set mydisp.FF5;
	 Year = int(Var1/100);
	 Month = Var1-Year*100;
run;
proc sql;
     create table mydisp.factor
	 as select a.*, b.umd from mydisp.FF5 as a, mydisp.factor as b
     where b.month = a.month
	 and b.year = a.year;
quit;
proc sql;
     create table mydisp.monthret_ff
	 as select * from mydisp.monthlyret as a, mydisp.factor as b
     where month(a.date) = b.month
	 and year(a.date) = b.year;
quit;
data mydisp.monthret_ff;
set mydisp.monthret_ff;
exret = ret - rf;
run;
/*NYSE breakpoints*/
PROC IMPORT datafile="&projpath.\data\nysebp.csv"
 	OUT=mydisp.nysebp
 	DBMS=csv
 	REPLACE;
 	GETNAMES=Yes;
RUN;
proc sql;
     create table mydisp.monthret_ff
	 as select a.*, b.nyse10bp, b.nyse20bp, b.nyse50bp from mydisp.monthret_ff as a, mydisp.nysebp as b
     where month(a.date) = b.month
	 and year(a.date) = b.year;
quit;
/*Annaul TFP*/
rsubmit;
%let beg_date = '01JAN1960'd;
%let end_date = '31DEC2019'd;
libname compm '/wrds/comp/sasdata/naa';
proc sql;
     create table comp3(keep = fyear gvkey fyr datadate at sich EMP dpact capx ebitda cogs ppegt ppent sale dp oibdp ceq xlr xsga xopr sppe invt PSTKL TXDITC PSTKRV SEQ PSTK) 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;
data comp3;
set comp3 (keep = fyear datadate gvkey fyr sich EMP dpact at capx ebitda cogs ppegt ppent sale dp oibdp ceq xlr xsga xopr sppe invt PSTKL TXDITC PSTKRV SEQ PSTK);
if fyr > 0;
if fyr <= 5 then cyear = fyear + 1;
else cyear = fyear;

/*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.;
/*create a book-to-market ratio or any other variables here;*/

/*Keep only relevant variables;*/
keep gvkey datadate begfyr endfyr fyr fyear sich at EMP dpact capx ebitda cogs ppegt ppent sale dp oibdp ceq xlr xsga xopr sppe invt PSTKL TXDITC PSTKRV SEQ PSTK;
run;
proc download data=comp3 out=mydisp.compustat_funda2;
run;
proc sql;
     create table comp2(keep=gvkey sic naics year1 year2) as
	 select a.* from compm.names a;
quit;
proc download data=comp2 out=mydisp.compustat_name;
run;
/* Download the CCM link table (requires a WRDS subscription; not distributed with this package) */
libname crspa '/wrds/crsp/sasdata/a_ccm';
proc sql;
     create table lnk as
     select * from crspa.ccmxpf_linktable;
quit;
proc download data=lnk out=mydisp.ccmxpf_linktable;
run;
endrsubmit;
signoff;
/* Added for the replication package: export the names file to Stata format */
proc export data=mydisp.compustat_name outfile="&projpath.\data\compustat_name.dta" replace;
run;
proc sort data=mydisp.compustat_funda2;
     by gvkey fyear;
run;
proc sort data=mydisp.compustat_funda2 out=mydisp.compustat_funda2;
  by gvkey descending fyear;
run;
data mydisp.compustat_funda2;
  set mydisp.compustat_funda2;
  by gvkey descending fyear;
  retain sic;
  if first.gvkey | not missing(sich) then
  sic=sich;
run;
proc sort data=mydisp.compustat_funda2;
  by gvkey fyear;
run;
data mydisp.compustat_funda2;
  set mydisp.compustat_funda2;
  by gvkey fyear;
  retain sic1;
  if first.gvkey | not missing(sic) then
  sic1=sic;
run;
proc sort data=mydisp.compustat_funda2;
  by gvkey fyear;
run;
data mydisp.compustat_funda2(drop = sic);
     set mydisp.compustat_funda2;
     rename sic1=sic;
run;
/*Merging compustat with linking table */
proc sql;
create table mydisp.compustat_funda2
as select * from mydisp.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;
data mydisp.compustat_funda2;
     set mydisp.compustat_funda2;
     PS = coalesce(PSTKRV,PSTKL,PSTK,0);
     if missing(TXDITC) then TXDITC = 0 ;
     BE = SEQ + TXDITC - PS ; 
run;
/*Adding CRSP data to Compustat*/
proc sql;
create table mydisp.compustat_funda2
as select * from mydisp.compustat_funda2 as a, mydisp.Monthret_ff as b
where a.lpermno = b.permno
and month(a.datadate) = month(b.date)
and year(a.datadate) = year(b.date)
and (exchcd = 1 or exchcd = 2 or exchcd = 3)
and (shrcd = 10 or shrcd = 11);
quit;
proc export data=mydisp.compustat_funda2 outfile="&projpath.\data\compustat_annual.dta" replace;
run;
