
/*****************************************************************/
/*create compustat variables for determinants model*/
/*****************************************************************/

libname m 'Path'; run;

data comp;
	set m.funda;
run;

data ind;
	set comp;
	if sich ne .;
run;

proc sort data=ind out=temp; by gvkeyn fyear; run;
proc sort data=temp nodupkey; by gvkeyn; run;

proc sql;
	create table comp1 as select a.*, b.sich as sich1, b.fyear as fyear1
	from comp as a left join temp as b
	on a.gvkeyn=b.gvkeyn
	order by a.gvkeyn, a.fyear;
quit;

proc sql;
	create table comp1 as select a.*, b.sich as lsich
	from comp1 as a left join comp as b
	on a.gvkeyn=b.gvkeyn and a.fyear=b.fyear+1;
quit;

data comp1;
	set comp1;
	if sich=. then sich=lsich;
run;

data names; set m.names; run;

proc sql;
	create table comp1 as select a.*, b.sicn
	from comp1 as a left join names as b
	on a.gvkeyn=b.gvkeyn;
quit;

proc sort data=comp1; by gvkeyn fyear; run;

data comp2;
	set comp1;
	if sich=. then sich=sicn;
	sic2 = substr(sich,9,2)*1;
	drop sicn sich1 lsich;
run;

proc import datafile='Path\CPI 2017.xlsx' 
	dbms=xlsx replace out=cpi;
	getnames=yes;
run;

proc sql;
	create table comp2 as select a.*, b.cpi/214.5646666667 as cpi
	from comp2 as a, cpi as b
	where a.fyear=b.year;
quit;

data compq;
	set m.fundq;
	if fyearq>=1995;
	if gvkeyn ne . and fyearq ne .;
	mbe = (cshoq*prccq)/ceqq;
	mb = (cshoq*prccq+atq-ceqq)/atq;
	blev = (dlttq+dlcq)/atq;
	cfa = (ibq+dpq)/atq;
	oroa = oibdpq/atq;
	mve = cshoq*prccq;
	keep gvkeyn fyearq fqtr datadate mbe mb blev cfa oroa mve rdq;
run;

proc sort data=compq nodupkey; by gvkeyn fyearq fqtr; run;

data compqa;
	set compq;
	obsq = (fyearq-2000)*4 + fqtr;
run;

proc sql;
	create table compq1 as select a.*, b.datadate as datadatef
	from compqa as a left join compqa as b
	on a.gvkeyn=b.gvkeyn and a.obsq=b.obsq-1;
quit;

data compq1;
	set compq1;
	if datadatef=. then datadatef=datadate+91;
run;

proc sql;
	create table comp2 as select a.*, b.*
	from compq1 as a, comp2 as b
	where a.gvkeyn=b.gvkeyn and a.fyearq=b.fyear;
quit;

data cnames;
	set m.msenames;
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 sort data=cnames nodupkey; 
	by permno namedt nameendt; 
run;

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

proc sql;
	create table comp3 as select a.*, b.shrcd
	from comp3 as a inner join cnames as b
	on a.permno=b.permno and b.namedt<=a.datadate<=b.nameendt;
quit;

libname r 'Path';
data link;
	set r.iclink_10302018;
	if score in (0,1,2,3);
	if permno ne .;
run;

data crspm;
	set m.msf;
	if 1990<=year(date);
run;

proc sql;
	create table comp4 as select a.*, b.ticker, b.score as ibescore
	from comp3 as a, link as b, crspm as c
	where a.permno=b.permno and b.permno=c.permno 
	and intnx('month',a.datadate,0,'E') = intnx('month',c.date,0,'E');
quit;

proc sort data=comp4 nodupkey; by gvkeyn datadate; run;

data ibes; 
	set m.statsum_epsus;
	if fpi in (6,7,8,9) and fiscalp="QTR";
	nmo = (year(statpers)-2000)*12+month(statpers);
	if year(statpers)>=1995;
	ESURP = (actual-medest)/abs(medest);
	ESURP1 = (actual-meanest)/abs(meanest);
	keep ticker actual fpi fiscalp  nmo statpers fpedats numest stdev medest meanest
	esurp esurp1;
run;

proc sort data=ibes out=ibes1 nodupkey; by ticker fiscalp fpedats statpers ; run;

proc sql;
	create table ibes2 as select distinct ticker, fpedats, avg(numest) as numest, 
	count(fpedats) as obs, avg(esurp) as esurp, avg(esurp1) as esurp1
	from ibes1 
	group by ticker, fpedats;
quit;

proc sql;
	create table comp4 as select a.*, b.numest, b.esurp, b.esurp1
	from comp4 as a left join ibes2 as b
	on a.ticker=b.ticker and year(a.datadate)=year(b.fpedats) 
	and month(a.datadate)=month(b.fpedats);
quit;

proc sort data=comp4 nodupkey; by gvkeyn datadate; run;

data det; 
	set m.det_guidance;
	if measure="EPS"; 
run;

data det1;
	set det;
	month = prd_mon;
	year = prd_yr;
	if range_desc in ("02","14","09") then point=1;
	if range_desc in ("01","08","06") then range=1;
	if range_desc in ("07","10","13","15","17") then max=1;
	if range_desc in ("03","04","05","11","12","16") then min=1;
	if point=1 then	Score=3;
	if range=1 then Score=2;
	if min=1 or max=1 then Score=1;
	if score in (2,3);
	mngfor = val_1;
	if val_2 ne . then mngfor = (val_1+val_2)/2;
	old=0;

	fordum=1;

	ID=_N_;
	fpe = mdy(month+1,1,year)-1;
	if month=12 then fpe = mdy(1,1,year+1)-1;
	format fpe date9.;
	pdicitys=substr(pdicity,1,1);

	if anndats<=fpe;

	keep ticker pdicity measure anndats month year score old guidance_code
	mean_at_date mngfor anntims val_1 val_2 fordum id fpe pdicitys; 

	/*All data in the Detail Guidance appears on a split-adjusted basis. This means that historical data appears on the same basis
	as current data. The data in the Split Guidance File is provided for those clients who wish to unsplit the data and use it as it
	originally appeared. */
run;

proc sort data=comp4 nodupkey; by gvkeyn datadate rdq; run;

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

proc sort data=comp4; by gvkeyn datadate rdq diff; run;
proc sort data=comp4 nodupkey; by gvkeyn datadate rdq; run;

proc sql;
	create table mng as select a.*, b.permno, b.score as ibescore
	from det1 as a, link as b, crspm as c
	where a.ticker=b.ticker and b.permno=c.permno 
	and intnx('month',a.anndats,0,'E') = intnx('month',c.date,0,'E');
quit;

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

proc sort data=mng; by id diff; run;
proc sort data=mng nodupkey; by id; run;

proc sql;
	create table mng1 as select distinct a.*, count(b.rdq) as bundled
	from mng as a left join comp4 as b
	on a.permno=b.permno and (a.tday-2)<=b.tday<=(a.tday+2)
	group by a.id;
quit;

data mng1;
	set mng1;
	if bundled ne . and bundled>1 then bundled=1;
	nmo = (year(anndats)-2000)*12+month(anndats);
run;

proc sort data=mng1 nodupkey; by id; run;

data comp5;
	set comp4;
	if datadate1=. then datadate1=datadate-91;
	nmo = (year(datadate)-2000)*12+month(datadate);
run;

/*create lags and leads of forecasting to determin which firms start and stop forecasting*/

proc sql;
	create table comp5 as select distinct a.*, max(b.fordum) as fordum0, max(b.bundled) as bundled0, 1-min(b.bundled) as nbundled0
	from comp5 as a left join mng1 as b
	on a.permno=b.permno and (a.datadate1)<b.anndats<=(a.datadate)
	group by a.gvkeyn, a.datadate;
quit;

data comp5;
	set comp5;
	if fordum0=. then fordum0=0;
	if bundled0=. then bundled0=0;
	if nbundled0=. then nbundled0=0;
run;

proc sql;
	create table comp5 as select distinct a.*, b.fordum0 as fordum1, b.bundled0 as bundled1, b.nbundled0 as nbundled1
	from comp5 as a left join comp5 as b
	on a.permno=b.permno and a.obsq=b.obsq+1;
quit;

proc sql;
	create table comp5 as select distinct a.*, b.fordum0 as fordum2, b.bundled0 as bundled2, b.nbundled0 as nbundled2
	from comp5 as a left join comp5 as b
	on a.permno=b.permno and a.obsq=b.obsq+2;
quit;

proc sql;
	create table comp5 as select distinct a.*, b.fordum0 as fordum3, b.bundled0 as bundled3, b.nbundled0 as nbundled3
	from comp5 as a left join comp5 as b
	on a.permno=b.permno and a.obsq=b.obsq+3;
quit;

proc sql;
	create table comp5 as select distinct a.*, b.fordum0 as fordum4, b.bundled0 as bundled4, b.nbundled0 as nbundled4
	from comp5 as a left join comp5 as b
	on a.permno=b.permno and a.obsq=b.obsq+4;
quit;

proc sql;
	create table comp5 as select distinct a.*, b.fordum0 as fordump1, b.bundled0 as bundledp1, b.nbundled0 as nbundledp1
	from comp5 as a left join comp5 as b
	on a.permno=b.permno and a.obsq=b.obsq-1;
quit;

proc sql;
	create table comp5 as select distinct a.*, b.fordum0 as fordump2, b.bundled0 as bundledp2, b.nbundled0 as nbundledp2
	from comp5 as a left join comp5 as b
	on a.permno=b.permno and a.obsq=b.obsq-2;
quit;

proc sql;
	create table comp5 as select distinct a.*, b.fordum0 as fordump3, b.bundled0 as bundledp3, b.nbundled0 as nbundledp3
	from comp5 as a left join comp5 as b
	on a.permno=b.permno and a.obsq=b.obsq-3;
quit;

proc sql;
	create table comp5 as select distinct a.*, b.fordum0 as fordump4, b.bundled0 as bundledp4, b.nbundled0 as nbundledp4
	from comp5 as a left join comp5 as b
	on a.permno=b.permno and a.obsq=b.obsq-4;
quit;

proc sort data=comp5 nodupkey; by gvkeyn datadate; run;

proc means data=comp5; run;

/*add return volatility*/

data temp;
	set comp5;
	nmo = (year(datadate)-2000)*12+month(datadate);
	keep permno datadate nmo;
run;

proc sort data=temp nodupkey; by permno datadate; run;

proc sql;
	create table crsp as select distinct a.*, b.vwretd, b.ewretd
	from m.msf as a, m.msi as b
	where a.date=b.date;
quit;

data crsp;
	set crsp;
	nmo = (year(date)-2000)*12+month(date);
run;

proc sql;
	create table crsp1 as select distinct a.permno, a.datadate, std(b.ret) as rvol, 
	count(b.ret) as obs, exp(sum(log(1+b.ret)))-exp(sum(log(1+b.vwretd))) as bharv,
	count(b.ret) as obs, exp(sum(log(1+b.ret)))-exp(sum(log(1+b.ewretd))) as bhar
	from temp as a, crsp as b
	where a.permno=b.permno and (a.nmo-11)<=b.nmo<=a.nmo
	group by a.permno, a.datadate;
quit;

proc sql;
	create table comp6 as select a.*, b.rvol, b.bharv, b.bhar
	from comp5 as a left join crsp1 as b
	on a.permno=b.permno and a.datadate=b.datadate;
quit;

proc sort data=comp6 nodupkey; by permno datadate; run;

/*instituional ownership*/

data io;
	set m.s34;
	if 1995<=year(rdate)<=2017;
	keep rdate fdate cusip mgrno shares;
run;

proc sort data=io nodupkey; by cusip mgrno rdate descending fdate; run;
proc sort data=io nodupkey; by cusip mgrno rdate; run;

data crspm;		
	set m.msf;
	if year(date)>=1995;
	keep permno date shrout prc ret cfacshr cfacpr;
run;

data cnames;		
	set m.msenames;
	if ncusip ne "";
run;

proc sort data=cnames nodupkey; 
	by permno namedt nameendt; 
run;

proc sql;
	create table crspm1 as select a.*, b.ncusip
	from crspm as a, cnames as b
	where a.permno=b.permno and b.namedt<=a.date<=b.nameendt;
quit;

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

proc sql;
	create table io1 as select a.*, b.permno, b.cfacshr
	from io as a, crspm1 as b
	where a.cusip=b.ncusip and year(a.fdate)=year(b.date) 
	and month(a.fdate)=month(b.date);
quit;

proc sql;
	create table io1 as select a.*, b.shrout, b.cfacshr as cfacshr1
	from io1 as a, crspm1 as b
	where a.cusip=b.ncusip and year(a.rdate)=year(b.date) 
	and month(a.rdate)=month(b.date);
quit;

proc sort data=io1 out=io1 nodupkey; by mgrno permno rdate; run;

data io2;
	set io1;
	pct = (shares*cfacshr)/(shrout*cfacshr1*1000);
	*if pct<0;
	if pct>1 then pct=.;
	nmo = (year(rdate)-2000)*12+month(rdate);
run;

proc sql;
	create table io3 as select distinct permno, nmo, rdate, sum(pct) as IO
	from io2
	group by permno, rdate
	order by permno, rdate;
quit;

data comp7;
	set comp6;
	nmo = (year(datadate)-2000)*12+month(datadate);
run;

proc sql;
	create table comp7 as select a.*, b.IO as io, a.nmo-b.nmo as ndiff
	from comp7 as a left join io3 as b
	on a.permno=b.permno and (a.nmo-2)<=b.nmo<=(a.nmo-0);
quit;

proc sort data=comp7; by gvkeyn datadate ndiff; run;
proc sort data=comp7 nodupkey; by gvkeyn datadate; run;

data comp8;
	set comp7;
	lnmve = log(mve/cpi);
	if numest=. then numest=0;
	lnanal = log(numest+1);
	if io=. then io=0;
	if io>1 then io=1;
	if fordum=. then fordum=0;
	if bundled=. then bundled=0;
	if nbundled=. then nbundled=0;

	drop esurp1;
run; 

/*create lags*/

proc sql;
	create table comp8 as select a.*, b.lnmve as lnmve1, b.blev as blev1, b.oroa as oroa1, b.mbe as mbe1, b.esurp as esurp1,
	b.bharv as bharv1, b.lnanal as lnanal1, b.io as io1, b.rvol as rvol1
	from comp8 as a left join comp8 as b
	on a.gvkeyn=b.gvkeyn and a.obsq=b.obsq+1;
quit;

data comp8;
	set comp8;
	year = year(datadate);
	if 2002<=year(datadate)<=2017;
	if lnmve1 ne . and blev1 ne . and oroa1 ne . and mbe1 ne . and esurp1 ne .
	and lnanal1 ne . and rvol1 ne . and bharv1 ne .;
	
	if shrcd in (10,11);
run; 


/*winsorize*/

options msglevel=i; %include 'Path\macros.sas';
%WT(data = comp8, out=comp8, byvar=none, 
	vars =  blev cfa oroa lnmve mb mbe lnanal rvol bharv bhar esurp blev1 oroa1 lnmve1 mbe1 lnanal1 rvol1 bharv1 esurp1,
		type = W, pctl = 1 99, drop=N);
run;

proc means data=comp8; run;

proc freq data=comp8;	
	table year*fordum;
quit;

data comp8;
	set comp8;
	*if fordum=. then fordum=0;
	y=year(datadate); m=month(datadate);
	if m in (1,2,3) then qtr=1;
	if m in (4,5,6) then qtr=2;
	if m in (7,8,9) then qtr=3;
	if m in (10,11,12) then qtr=4;
	qobs = (y-2000)*4+qtr;
run;


/*create market downturns*/

data comp9;
	set comp8;
	y=year(datadate); m=month(datadate);
	if m in (1,2,3) then qtr=1;
	if m in (4,5,6) then qtr=2;
	if m in (7,8,9) then qtr=3;
	if m in (10,11,12) then qtr=4;
	qnmo = (y-2000)*4+qtr;
	nmo=(year(datadate)-2000)*12+month(datadate);
	nmof=(year(datadatef)-2000)*12+month(datadatef);
run;

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 comp9 as select a.*, b.recession
	from comp9 as a left join rec as b
	on b.bnmo<=a.nmo<=b.enmo;
quit;

data comp9;
	set comp9;
	if recession=. then recession=0;
run;

proc means data=comp9; run;

proc sort data=comp9 nodupkey; by gvkeyn qobs; run;

proc export data=comp9
	outfile= "Path\Determinants Forecasts.dta" 
	replace;
run; 

