
/*THIS PROGRAM CALCULATES DTURN AND OTHER STOCK RETURNS-BASED VARIABLES*/
/*CALCULATE MONTHLY ABNORMAL TURNOVER ACCORDING TO KIM ET AL. (2011): DTURN)*/
rsubmit;
proc upload data=index out=index; run;
data a0; set index; where 1985<=year<=2017; run;
proc sql;
  create table a1 as select
    a.gvkey, a.year, b.vol, b.shrout, b.date
  from a0 a left join crsp.msf b
  on a.permno=b.permno and
  	 a.fybegdt<=b.date<=a.fyenddt and
	 b.ret~=.; quit;
proc sort data=a1 nodupkey; by gvkey year date; run;
data a2; set a1;
turnover=vol/shrout; run;
proc means data=a2 noprint; by gvkey year;
  output out=a2stats mean(turnover)=turnover; run;
data a2stats1; set a2stats;
proc sql;
  create table a3 as select
    a.gvkey, a.year, a._freq_ as mthfreq, a.turnover, b._freq_ as mthfreqlag, b.turnover as turnoverlag
  from a2stats a left join a2stats1 b
  on a.gvkey=b.gvkey and
  	 a.year-1=b.year; quit;
proc sort data=a3 nodupkey; by gvkey year; run;
data dturn (drop=turnover turnoverlag); set a3;
dturn=turnover-turnoverlag; 
label	dturn='Change in average monthly share turnover during the fiscal year'
		mthfreq='Number of months used to calculate current year share turnover'
		mthfreqlag='Number of months used to calculate prior year share turnover'; run;
proc download data=dturn out=dturn; run;
endrsubmit;

PROC EXPORT DATA= WORK.Dturn 
            OUTFILE= "C:\JAR Files\dturn.dta" 
            DBMS=STATA REPLACE;
RUN;

/*COLLECT DATA FOR RETURNS AND RETURNS VOLATILITY*/
rsubmit;
data a1(keep=gvkey fyear datadate); set comp.funda;
where consol='C' and indfmt='INDL' and datafmt='STD' and popsrc='D' and curcd = 'USD' and 1985<=fyear<=2017; run;
/*DEFINE OPENING WINDOWS TO COLLECT MONTHLY RETURNS*/
data a1 (drop=datadate); set a1;
fybegdt=intnx('month',datadate,-12,'END')+1;
fyenddt=datadate;
format fybegdt fyenddt date9.; 
rename fyear=year; run;
/*OBTAIN PERMNO FROM CRSP-COMPUSTAT LINK FILE*/
proc sql;
  create table a2 as select
    a.*, b.lpermno as permno, b.linkdt, b.linkenddt
  from a1 a, crsp.ccmxpf_linktable b
  where a.gvkey=b.gvkey and
	    b.linktype in ("LU" "LC" "LD" "LF" "LN" "LO" "LS" "LX") and 
	   (b.LINKDT <= a.fyenddt or b.LINKDT = .B) and 
       (a.fyenddt <= b.LINKENDDT or b.LINKENDDT = .E) and
	    b.usedflag=1 and
		b.lpermno~=.; quit;
proc sort data=a2; by gvkey year descending linkenddt;
proc sort data=a2 out=a2 (drop=linkdt linkenddt) nodupkey; by gvkey year; run;
/*COLLECT ANNUAL RETURNS FROM CRSP USING MONTHLY DATA*/
proc sql;
  create table a3 as select
    a.*, b.ret, b.date
  from a2 a left join crsp.msf b
  on a.permno=b.permno and
  	 a.fybegdt<=b.date<=a.fyenddt and
	 b.ret~=.; quit;
proc sort data=a3 nodupkey; by gvkey year date; run;
data a3; set a3; 
if ret<-1 then delete; run;
proc means data=a3 noprint;
  by gvkey year;
  output out=a3stats sum(ret)=ret stddev(ret)=retvol; run;
/*CREATE RETURNS TABLE: REQUIRE ALL 12 MONTHS TO BE AVAILABLE*/
proc sql;
  create table returns as select
    a.gvkey, a.year, a.permno, b.ret, b.retvol
  from a2 a, a3stats b
  where a.gvkey=b.gvkey and
  		a.year=b.year and
		b._freq_=12; quit;
proc sort data=returns nodupkey; by gvkey year; run;
data returns; set returns;
if ret=. then delete;
label	ret='12-month returns over the fiscal year'
		retvol='12-month returns volatility over the fiscal year'; run;
proc download data=returns out=returns; run;
endrsubmit;

PROC EXPORT DATA= WORK.returns 
            OUTFILE= "C:\JAR Files\returns.dta" 
            DBMS=STATA REPLACE;
RUN;
