/*This program roughly replicates a study by Richardson, Sloan, Soliman, and Tuna (JAE 2005).
  It only replicates Tables 3-5.  No market data is included as in RSST (2005);*/

*runs a macro to connect to WRDS;
%wrds;


rsubmit;

*pull data from compustat XPRESSFEED via WRDS;

*require firms to have non-missing assets, cash, current assets, current liabilities, 
 sales, total liabilities, and operating income.

*require firms to have an end of the year stock price greater than $1.  I do this because
 I'm not matching to CRSP like RSST(2005) do, but this at least requires that the firm be
 traded, so you get close to the same number of observations;

proc sql;
   create table comp1
	as select gvkey,
	          datadate,
				 ACT,
				 CHE,
             LCT,
				 DLC,
				 AT,
				 IVAO,
				 LT,
				 DLTT,
				 IVST,
				 DLC,
				 PSTK,
				 OIADP
	from comp.funda
	where INDFMT='INDL'and DATAFMT='STD'and POPSRC='D' and CONSOL='C'
	   and 1962 <= year(datadate) <=  2001
		and missing(AT)=0
      and missing(CHE)=0
      and missing(ACT)=0
      and missing(LCT)=0
      and missing(SALE)=0
      and missing(LT)=0
      and missing(OIADP)=0
      and prcc_f > 1;
	quit;

*pull SIC codes so I can exclude financial firms later;
proc sql;
   create table comp1
	as select comp1.gvkey,
	          comp1.datadate,
				 company.SIC,
				 comp1.*
	from comp1 left join comp.company
	on comp1.gvkey = company.gvkey;
   quit;

*download the data to PC for analysis;
proc download data=comp1;
   run;
endrsubmit;


*set some missing values to zero (see RSST(2005) footnote 8 for details);
data comp2;
   set comp1;
	array zz(*) DLTT DLC PSTK IVST IVAO;
	do i=1 to dim(zz);
	 if missing(zz(i)) then zz(i)=0;
	end;
	run;


*calculate variables, scaling by average total assets.  See RSST(2005) for details.;
*eliminate firms with SIC codes in the 6000s;
proc sql;
   create table comp3
	as select a.gvkey,
	          a.datadate,
				 a.SIC,
				 a.OIADP/((a.AT+b.AT)/2) as ROA,
				 ((a.ACT-a.CHE)-(b.ACT-b.CHE))/((a.AT+b.AT)/2) as dCOA,
				 (((a.LCT-a.DLC)-(b.LCT-b.DLC))/((a.AT+b.AT)/2))*-1 as dCOL,
				 (calculated dCOA) + (calculated dCOL) as dWC,
				 ((a.AT-a.ACT-a.IVAO) - (b.AT-b.ACT-b.IVAO))/((a.AT+b.AT)/2) as dNCOA,
				 (((a.LT-a.LCT-a.DLTT) - (b.LT-b.LCT-b.DLTT))/((a.AT+b.AT)/2))*-1 as dNCOL,
				 (calculated dNCOA) + (calculated dNCOL) as dNCO,
				 (a.IVST-b.IVST)/((a.AT+b.AT)/2) as dSTI,
				 (a.IVAO-b.IVAO)/((a.AT+b.AT)/2) as dLTI,
				 ((a.IVST+a.IVAO)-(b.IVST+b.IVAO))/((a.AT+b.AT)/2) as dFINA,
				 (((a.DLTT+a.DLC+a.PSTK) - (b.DLTT+b.DLC+b.PSTK))/((a.AT+b.AT)/2))*-1 as dFINL,
				 (calculated dFINA)+(calculated dFINL) as dFIN,
				 (calculated dWC) + (calculated dNCO) + (calculated dFIN) as TACC
   from comp2 as a, comp2 as b
   where a.gvkey = b.gvkey
     and year(a.datadate) = year(b.datadate)+1
     and substr(a.SIC,1,1) ne "6";
   quit; 

*winsorize at 1 and -1;
data comp3;
   set comp3;
	array win(*) ROA dCOA dCOL dWC dNCOA dNCOL dNCO dFINA dFINL dFIN TACC dSTI dLTI;
	do i=1 to dim(win);
	  if win(i) > 1 then win(i)=1;
	  if win(i) < -1 then win(i)=-1;
	  end;
	run;

*eliminate exact duplicates;
proc sort data=comp3 noduplicates;
   by gvkey datadate;
	run;

*check for duplicates on gvkey and datadate;
proc sort data=comp3 nodupkey dupout=check;
   by gvkey datadate;
	run;

*remove any observations that have duplicates on gvkey and datadate
	 I'm being lazy here, and should actually figure out why there are duplicates,
	but just decided to eliminate them because there are not very many;
data comp3;
   merge comp3(in=a) check(in=b);
	by gvkey datadate;
	if b=1 then delete;
	run;
*keep only firms that have two years of data.  I do this because in the next step I need to calculate 
	 a lead value;
proc sql;
   create table comp3
	as select *
	from comp3
	group by gvkey
	having n(gvkey) >= 2
   order by gvkey,datadate;
	quit;

*calculate lead of ROA;
proc expand data=comp3 out=comp3 method=none;
  by gvkey;
  id datadate;
  convert ROA = ROA_lead1 / transform=(lead 1);
  run;

*delete observations where the lead of ROA is missing;
data comp3;
   set comp3;
	where ROA_lead1 ne .;
	run;

*get descriptive stats and send the results to an excel file;
ods html file="H:\_Research\Replications\RSST 2005 JAE\Results\T3 Panel A.XLS";
proc means data=comp3 n mean std p25 p50 p75;
   title "Descriptive Statistics";
	var TACC dWC dNCO dFIN ROA ROA_lead1;
	run;
ods html close;

*calculate the pairwise correlations;
*this uses a macro for formatting purposes.  This will not work unless you have my file of macros.
 The statement below (proc corr) will do the same thing;
ods html file="H:\_Research\Replications\RSST 2005 JAE\Results\T3 Panel B.XLS";
%corrps(data=comp3, vars=TACC dWC dNCO dFIN ROA ROA_lead1);
ods html close;

proc corr data=comp3 pearson spearman;
   title "Univariate Correlations";
	var TACC dWC dNCO dFIN ROA ROA_lead1;
	run;


*run the persistence regressions;
ods html file="H:\_Research\Replications\RSST 2005 JAE\Results\T5 Panels A and B.XLS";
proc reg data=comp3;
   title "Regression Analysis for Table 5";
   model ROA_lead1 = ROA;
	model ROA_lead1 = ROA TACC;
   model ROA_lead1 = ROA dWC;
   model ROA_lead1 = ROA dNCO;
   model ROA_lead1 = ROA dFIN;
   model ROA_lead1 = ROA dWC dNCO dFIN;
	run;
	quit;
ods html close;


*re-do the above analyses with the more refined partitioning of accruals;
ods html file="H:\_Research\Replications\RSST 2005 JAE\Results\T4 Panel A.XLS";
proc means data=comp3 n mean std p25 p50 p75;
   title "Descriptive Statistics";
	var TACC dCOA dCOL dNCOA dNCOL dSTI dLTI dFINL ROA ROA_lead1;
	run;
ods html close;

*calculate the pairwise correlations;
*this uses a macro for formatting purposes.  This will not work unless you have my file of macros.
 The statement below (proc corr) will do the same thing;
ods html file="H:\_Research\Replications\RSST 2005 JAE\Results\T4 Panel B.XLS";
%corrps(data=comp3, vars=TACC dCOA dCOL dNCOA dNCOL dSTI dLTI dFINL ROA ROA_lead1);
ods html close;

proc corr data=comp3 pearson spearman;
   title "Univariate Correlations";
	var TACC dCOA dCOL dNCOA dNCOL dSTI dLTI dFINL ROA ROA_lead1;
	run;
ods html file="H:\_Research\Replications\RSST 2005 JAE\Results\T5 Panel C.XLS";
proc reg data=comp3;
   title "Regression Analysis for Table 5";
   model ROA_lead1 = ROA;
   model ROA_lead1 = ROA dCOA;
   model ROA_lead1 = ROA dCOL;
   model ROA_lead1 = ROA dNCOA;
   model ROA_lead1 = ROA dNCOL;
   model ROA_lead1 = ROA dSTI;
   model ROA_lead1 = ROA dLTI;
   model ROA_lead1 = ROA dFINL;
   model ROA_lead1 = ROA dCOA dCOL dNCOA dNCOL dSTI dLTI dFINL;
	run;
	quit;
ods html close;