
/*THIS PROGRAM COMPUTES NCSKEW, DUVOL AND CRASH*/
rsubmit;
proc upload data=index out=index; run;
proc sql;
  create table a1 as select
    a.gvkey, a.year, a.permno, a.fybegdt, a.fyenddt, b.ret, b.date
  from index a left join crsp.dsf b
  on a.permno=b.permno and
  	 a.fybegdt<=b.date<=a.fyenddt; quit;
proc sort data=a1 nodupkey; by gvkey year date; run;
proc sql;
  create table a2a as select
    a.*, b.vwretd
  from a1 a, crsp.dsi b
  where a.date=b.date; quit;
proc sort data=a2a nodupkey; by gvkey year date; run;
proc sql;
  create table a2 as select
    a.*, b.decret
  from a2a a left join crsp.erdport1 b
  on a.permno=b.permno and
	 a.date=b.date; quit;
proc sort data=a2 nodupkey; by gvkey year date; run;
/*CREATE WEEK VARIABLE THAT STARTS ON WEDNESDAY AND ENDS ON TUESDAY*/
data a2; set a2;
weekday=weekday(date);
format date date9.; run;
data a2; set a2;
start=0; if weekday=3 then start=1; run;
data a2; set a2; 
by gvkey year;
retain week;
if first.year then week=0;
if start=1 then week=week+1; run;
proc means data=a2 noprint;
  by gvkey year week;
  output out=a3 sum(ret)=wret sum(vwretd)=wvret; run;
data a3(drop=_type_ _freq_); set a3;
vcar=wret-wvret; /*CUMULATIVE ABNORMAL RETURNS*/
/*DELETE WEEK-OBSERVATIONS WITH FREQ=1*/               /*DESIGN CHOICE*/ 
if _freq_=1 then delete; run;
/*CREATE COUNT TO TRACK WEEKS: THIS COUNT VARIABLE ALLOWS RUNNING CRASH MODEL WHERE WEEKS SPANS ACROSS 2 FISCAL YEARS*/
data a3; set a3;
by gvkey; retain count;
if first.gvkey then count=0;
count=count+1; run;
/*CREATE LAGGED AND LEAD VARIABLES*/
data a4; set a3; run;
proc sql;
  create table a4a as select
    a.*, b.wvret as wvretlag2
  from a3 a left join a4 b
  on a.gvkey=b.gvkey and
  	 a.count-2=b.count; quit;
proc sort data=a4a nodupkey; by gvkey year week; run;
proc sql;
  create table a4b as select
    a.*, b.wvret as wvretlag1
  from a4a a left join a4 b
  on a.gvkey=b.gvkey and
  	 a.count-1=b.count; quit;
proc sort data=a4b nodupkey; by gvkey year week; run;
proc sql;
  create table a4c as select
    a.*, b.wvret as wvretld1
  from a4b a left join a4 b
  on a.gvkey=b.gvkey and
  	 a.count+1=b.count; quit;
proc sort data=a4c nodupkey; by gvkey year week; run;
proc sql;
  create table a5 as select
    a.*, b.wvret as wvretld2
  from a4c a left join a4 b
  on a.gvkey=b.gvkey and
  	 a.count+2=b.count; quit;
proc sort data=a5 nodupkey; by gvkey year week; run;
/*ESTMATE CAR CRASH MODEL: VALUE-WEIGHTED MARKET INDEX*/
proc reg data=a5 outest=model_car tableout rsquare noprint;
  by gvkey year;
  model wret=wvretlag2 wvretlag1 wvret wvretld1 wvretld2;
  output out=car_resid residual=car_resid; run;
data model_car; set model_car; where _type_='PARMS'; run;
data car_resid; set car_resid;
wcar=log(1+car_resid); 
wcar2=wcar**2;
wcar3=wcar**3; run;
proc means data=car_resid noprint; by gvkey year;
  output out=car_resid_stats stddev(wcar)=sdwcar mean(wcar)=meanwcar; run;
/*MERGE ALL DATASETS FOR CAR TOGETHER*/
proc sql;
  create table a6 as select
    a.gvkey, a.year, a.week, a.wret, a.vcar, a.wcar, a.wcar2, a.wcar3, b.sdwcar, b.meanwcar
  from car_resid a left join car_resid_stats b
  on a.gvkey=b.gvkey and
  	 a.year=b.year; quit;
proc sort data=a6 nodupkey; by gvkey year week; run;
/*3.09 IS CHOSEN TO FOLLOW HUTTON ET AL. (2009)*/
data a6; set a6;
crashline=meanwcar-3.09*sdwcar;
crash=0; if wcar<crashline then crash=1; if wcar=. then crash=.; run;
/*OBTAIN NUMBER OF CRASHES AND SUM(WCAR2) & SUM(WCAR3) BY YEAR*/
proc means data=a6 noprint; by gvkey year; 
  output out=a6stats sum(crash)=sumcrash sum(wcar2)=wcar2 sum(wcar3)=wcar3; run;
/*CALCULATE STD DEV OF UP AND DOWN WEEKS TO CALUCULATE DUVOL*/
data a6up; set a6; where wcar>meanwcar;
proc means data=a6up noprint; by gvkey year;
  output out=a6upstats stddev(wcar)=stdup; run;
data a6down; set a6; where wcar<meanwcar;
proc means data=a6down noprint; by gvkey year;
  output out=a6downstats stddev(wcar)=stddown; run;
/*MERGE CAR MODEL TOGETHER*/
proc sql;
  create table carcrash1 as select
    a.*, b._edf_, b._rsq_ as rsq
  from a6stats a left join model_car b
  on a.gvkey=b.gvkey and
  	 a.year=b.year; quit;
proc sort data=carcrash1 nodupkey; by gvkey year; run;
data carcrash1 (drop=_type_ _edf_ wcar2 wcar3); set carcrash1;
crash_car=0; if sumcrash>0 then crash_car=1; if sumcrash=. then crash_car=.;
n=_edf_+6;
ncskew_car=-(n*((n-1)**1.5)*wcar3)/((n-1)*(n-2)*(wcar2**1.5)); run;
/*OBTAIN DUVOL VARIABLES*/
proc sql;
  create table carcrash2a as select
    a.*, b.stdup
  from carcrash1 a left join a6upstats b
  on a.gvkey=b.gvkey and
  	 a.year=b.year; quit;
proc sort data=carcrash2a nodupkey; by gvkey year; run;
proc sql;
  create table carcrash2 as select
    a.*, b.stddown
  from carcrash2a a left join a6downstats b
  on a.gvkey=b.gvkey and
  	 a.year=b.year; quit;
proc sort data=carcrash2 nodupkey; by gvkey year; run;
data carcrash2(drop=stddown stdup); set carcrash2;
duvol_car=log(stddown/stdup); run;
/*COMBINE DATASETS TOGETHER*/
proc sql;
  create table carcrash as select
    a.*, b.meanwcar as retcar, b.sdwcar as sigmacar
  from carcrash2 a left join car_resid_stats b
  on a.gvkey=b.gvkey and
  	 a.year=b.year; quit;
proc sort data=carcrash nodupkey; by gvkey year; run;
/*CRASH DATASET*/
data crash (drop=rsq _freq_ sumcrash retcar sigmacar); set carcrash;
rename n=n_car; run;
data crash; set crash;
label	crash_car="Indicator equals one if a crash occurs in a fiscal year, based on CAR"
		n_car="Number of weeks used to compute crashes, based on CAR"
		ncskew_car="Negative skewness of returns, based on CAR"
		duvol_car="DUVOL, based on CAR"; run;
proc download data=crash out=crash; run;
/*RET DATASET*/
data ret (drop=rsq _freq_ n sumcrash crash_car ncskew_car duvol_car); set carcrash; 
label	retcar="Average weekly stock returns during the fiscal year"
		sigmacar="Std Deviation of weekly stock returns during the fiscal year"; run;
proc download data=ret out=ret; run;
endrsubmit;

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

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