/*************************************/
/*Replicate Loh and Stulz (2018)*/
/*************************************/
/*get daily returns and other crsp data*/

libname m 'Path'; run;

data crsp;
	set m.dsf;
	if year(date)>=2000;
	prc=abs(prc);
	mve=prc*shrout;
	keep permno date mve cfacshr prc cfacpr ret;
run;

data tday; set m.tday; run;

proc sql;
	create table crsp1 as select a.*, b.tday
	from crsp as a, tday as b
	where a.date=b.date;
quit;

proc sql;
	create table crsp2 as select a.*, b.prc as prc1
	from crsp1 as a, crsp1 as b
	where a.permno=b.permno and a.tday=b.tday+1;
quit;

proc sql;
	create table crsp2 as select distinct a.*, b.shrcd, b.siccd
	from crsp2 as a inner join m.msenames as b
	on a.permno=b.permno and (b.namedt<=a.date<=b.nameendt) and b.shrcd in (10,11);
quit;

/*merge mangement and analyst forecast data onto daily returns*/

proc import datafile= "Path\Management Forecast News JAR.dta"
	out=mng1a replace;
quit;

proc import datafile= "Path\Management vs Analyst Value Full1.dta"
	out=mng1 replace;
quit;

data mng1a;
	set mng1a;
	eid = id + 10000000000;
	mng=1;
	fpedats=fpe;
run;

data mng2;
	set mng1 mng1a;
	year = year(anndats);
	if 2002<=year<=2017;
	if pdicitys in ("A","Q");

	if mng=0 then value=value/cfacshr;
	if value=. then value=valuea;

	news1 = (value-medest30)/abs(medest30);
	if bundled=1 then news1 = (value-(medest30+revadjm))/abs(medest30+revadjm);
run; 

proc sort data=mng2 nodupkey; by eid; run;

proc sql;
	create table mng2 as select distinct permno, anndats, tday, mng, mean(news1) as news1
	from mng2
	group by permno, tday, mng;
quit;

data mng2;
	set mng2;
	year=year(anndats);
run;

options msglevel=i; %include 'Path\macros.sas';
%WT(data = mng2, out=mng2, byvar=mng, 
	vars = news1,
		type = W, pctl = 1 99, drop=N);
run;

proc sql;
	create table crsp3 as select distinct a.*, b.mng, b.news1 as news1m
	from crsp2 as a left join mng2 as b
	on a.permno=b.permno and a.tday=b.tday and b.mng=1;
quit;

proc sql;
	create table crsp3 as select distinct a.*, 1-b.mng  as anal, b.news1 as news1a
	from crsp3 as a left join mng2 as b
	on a.permno=b.permno and a.tday=b.tday and b.mng=0;
quit;

data crsp3a;
	set crsp3;
	if news1m=. and mng in (.) then news1m=0;
	if news1a=. and anal in (.) then news1a=0;
	if mng=. then mng=0;
	if anal=. then anal=0;
	nmo = (year(date)-2000)*12+month(date);
	if 2002<=year(date)<=2017;
run;

/*add recession variable*/

proc import datafile="Path\NBER Recession Dates.xlsx"
	replace out=rec dbms=xlsx;
	getnames=yes;
quit;

data rec;
	set rec;
	bnmo = (byear-2000)*12+bmonth;
	enmo = (eyear-2000)*12+emonth;
	recession=1;
run;

proc sql;
	create table crsp4 as select a.*, b.recession
	from crsp3a as a left join rec as b
	on b.bnmo<=a.nmo<=b.enmo;
quit;

/*add Fama-French industries*/

%include 'Path\macros.sas';
%ff30(data=crsp4,newvarname=ff30,sic=siccd,out=crsp4);

data crsp4;
	set crsp4;
	if recession=. then recession=0;
	
	fcst=0;
	if anal=1 then fcst=1;
	if mng=1 then fcst=2;
	absret = abs(ret);

	if ff30 ne .;

	if absret ne .;
run;

proc sort data=crsp4 nodupkey; by permno date; run;

proc means data=crsp4; run;

proc datasets lib=work;
	modify crsp4;
	rename PERMNO=permno;
	rename DATE=date;
	rename RET=ret;
quit;

/*add indicators for other news events*/

/*earnings announcement*/

data compq;		
	set m.fundq;
	if rdq ne .;
	ea=1;
	keep gvkeyn fyearq fqtr datadate RDQ ea;
run;

proc sort data=compq nodupkey; by gvkeyn rdq; run;

proc sql;
	create table compq1 as select a.*, b.tday, b.date-a.rdq as diff
	from compq as a, tday as b
	where a.rdq<=b.date<=(a.rdq+4);
quit;

proc sort data=compq1; by gvkeyn rdq diff; run;
proc sort data=compq1 nodupkey; by gvkeyn rdq; run;


data link1;
	set m.CCMXPF_LINKTABLE;	
	permno=lpermno;
	gvkeyn=gvkey*1;
	if usedflag=1;
	if linkenddt=.E then linkenddt = input ('20250802',  yymmdd8.);
	if linkdt=.B then linkdt = input ('19000101',  yymmdd8.);
	if linkprim in ("P", "C");
run;

proc sort data=link1 nodupkey;
	by gvkeyn permno linkdt linkenddt;
run;

proc sql;
	create table crsp5 as select a.*, b.gvkeyn
	from crsp4 as a inner join link1 as b
	on a.permno=b.permno and b.linkdt<=a.date<=b.linkenddt;
quit;

proc sql;
	create table crsp5 as select distinct a.*, coalesce(b.ea,0) as ea
	from crsp5 as a left join compq1 as b
	on a.gvkeyn=b.gvkeyn and a.tday=b.tday;
quit;

/*dividend*/

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

rsubmit;
data dse;
	set crsp.dsedist;
run;

proc download data=dse; run;
endrsubmit;

data dse1;
	set dse;
	if substr(distcd,9,1)*1 in (1,2,5);
	div=1;
run;

proc sql;
	create table dse1 as select a.*, b.tday, b.date-a.dclrdt as diff
	from dse1 as a, tday as b
	where a.dclrdt<=b.date<=(a.dclrdt+4);
quit;

proc sort data=dse1; by permno dclrdt diff; run;
proc sort data=dse1 nodupkey; by permno dclrdt; run;

proc sql;
	create table crsp5 as select distinct a.*, coalesce(b.div,0) as div
	from crsp5 as a left join dse1 as b
	on a.permno=b.permno and a.tday=b.tday;
quit;

/*insider trades*/

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

rsubmit;
data t1;
	set tfn.table1;
	year=year(trandate);
	if year>=2000;
	if formtype="4";
	if cleanse ne "A";
	if cleanse ne "S";
	if trancode in ("S", "P");
	if ownership ne "I";
	if shares ne .;
	if amend ne "A";
	keep fdate trandate cusip6 trancode;
run;

proc download data=t1; run;
endrsubmit;

rsubmit;
data t2;
	set tfn.table2;
	year=year(trandate);
	if year>=2000;
	if formtype="4";
	if cleanse ne "A";
	if cleanse ne "S";
	if trancode in ("S", "P");
	if ownership ne "I";
	if shares ne .;
	if amend ne "A";
	keep fdate trandate cusip6 trancode;
run;

proc download data=t2; run;
endrsubmit;

data ins;
	set t1 t2;
run;

proc sql;
	create table ins as select distinct a.*, b.permno
	from ins as a inner join m.msenames as b
	on a.cusip6=substr(b.ncusip,1,6) and (b.namedt<=a.fdate<=b.nameendt);
quit;

proc sort data=ins nodupkey; by permno fdate trandate; run;

proc sql;
	create table ins1 as select a.*, b.tday as ftday, b.date-a.fdate as diff
	from ins as a, tday as b
	where a.fdate<=b.date<=(a.fdate+4);
quit;

proc sort data=ins1; by permno fdate diff; run;
proc sort data=ins1 nodupkey; by permno fdate; run;
proc sort data=ins1 nodupkey; by permno ftday; run;

proc sql;
	create table ins2 as select a.*, b.tday as ttday, b.date-a.trandate as diff
	from ins as a, tday as b
	where a.trandate<=b.date<=(a.trandate+4);
quit;

proc sort data=ins2; by permno trandate diff; run;
proc sort data=ins2 nodupkey; by permno trandate; run;
proc sort data=ins2 nodupkey; by permno ttday; run;

data ins1; set ins1; fins=1; run;
data ins2; set ins2; tins=1; run;

proc sql;
	create table crsp5 as select distinct a.*, coalesce(b.fins,0) as fins
	from crsp5 as a left join ins1 as b
	on a.permno=b.permno and a.tday=b.ftday;
quit;

proc sql;
	create table crsp5 as select distinct a.*, coalesce(b.tins,0) as tins
	from crsp5 as a left join ins2 as b
	on a.permno=b.permno and a.tday=b.ttday;
quit;

proc sort data=crsp5 nodupkey; by permno date; run;

proc means data=crsp5; run;

proc export data=crsp5
	outfile= "Path\Loh and Stulz.dta" 
	replace;
run; 
