/*****************************************************************************************/
/*create sample of management forecasts*/
/*****************************************************************************************/
libname m 'Path'; run;

data ibes; set m.detu_epsus; run;
data exc; set m.excu_epsus; run;
data stop; set m.stopu_epsus; run;

proc freq data=ibes; table fpi; run;

data ibes1;
	set ibes;
	if fpi in (1,2,3,4,5) then pdicity="A";
	if fpi in (6,7,8,9) then pdicity="Q";
	if pdicity in ("A", "Q");
	if ticker ne "" and anndats ne . and fpedats ne .;
run;

data stop1;
	set stop;
	if measure="EPS";
	stop=1;
run;

proc sql;
	create table ibes2 as select a.*, b.astpdats
	from ibes1 as a left join stop1 as b
	on a.ticker=b.ticker and a.fpedats=b.fpedats and a.estimator=b.estimator
	and a.pdicity=b.pdicity and b.astpdats>a.actdats;
quit;

data exc1;
	set exc;
	if measure="EPS";
	exc=1;
	if fpi in (1,2,3,4,5) then pdicity="A";
	if fpi in (6,7,8,9) then pdicity="Q";
run;

proc sql;
	create table ibes2 as select a.*, b.excdats
	from ibes2 as a left join exc1 as b
	on a.ticker=b.ticker and a.fpedats=b.fpedats and a.estimator=b.estimator
	and a.pdicity=b.pdicity and a.actdats=b.actdats and a.acttims=b.acttims;
quit;

proc sort data=ibes2 out=ibes3 nodupkey;
	by ticker fpedats estimator analys actdats acttims;
run;

data ibes3;
	set ibes3;
	EID = _n_;
run;

/*add permno identifiers*/

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; run;

proc sql;
	create table ibes4 as select a.*, b.permno, b.score as ibescore
	from ibes3 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 sort data=ibes4 nodupkey; by eid; run;

data crsp; set m.dsf; run;

proc sql;
	create table ibes5 as select a.*, b.cfacshr, a.anndats-b.date as diff
	from ibes4 as a left join crsp as b
	on a.permno=b.permno and (a.anndats-4)<=b.date<=(a.anndats+0);
quit;

proc sort data=ibes5; by eid diff; run;
proc sort data=ibes5 nodupkey; by eid; run;

data m.ibes5; set ibes5; 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;
	keep ticker pdicity measure anndats month year score old guidance_code
	mean_at_date mngfor anntims val_1 val_2; 

	/*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.*/
run;

data mng;
	set det1;
	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);
run;

proc sql;
	create table mng as select a.*, b.permno, b.score as ibescore
	from mng 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 sort data=mng nodupkey; by id; run;

/*calculate analyst concensus*/

proc sql;
	create table temp as select a.*, b.value, b.cfacshr, b.eid, b.estimator, b.analys, 
	b.anndats as ianndats, b.astpdats, b.excdats, b.anntims as ianntims
	from mng as a inner join ibes5 as b
	on a.ticker=b.ticker and a.pdicitys=b.pdicity and a.fpe=b.fpedats 
	and (a.anndats-30)<=b.anndats<a.anndats
	order by a.id, b.anndats;
quit;

data temp;
	set temp;
	value = value/cfacshr;
	if astpdats ne . and astpdats<anndats then delete;
	if excdats ne . and excdats<anndats then delete;
run;

proc sort data=temp;
	by id estimator ianndats ianntims;
run;

data temp;
	set temp;
	by id estimator ianndats ianntims;
	if last.estimator;
run;

proc means data=temp noprint;
	by id;
	var value;
	output out=temp1 mean=meanest median=medest n=numest std=stddev;
quit;

proc sql;
	create table mng1 as select a.*, b.meanest as meanest30, b.numest as numest30,
	b.stddev as stddev30, b.medest as medest30
	from mng as a left join temp1 as b
	on a.id=b.id;
quit;

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

data mng2;
	set mng1;
	anndatso=anndats;
	format anndatso date9.;
	if anntims>'16:00:00't then anndats=anndats+1;
	year=year(anndats);
run;

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 mng3 as select a.*, b.gvkeyn
	from mng2 as a inner join link1 as b
	on a.permno=b.permno and b.linkdt<=a.anndats<=b.linkenddt;
quit;

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


/*calculate CARs/BHARs using Eventus*/

data events;
	retain permno anndats;
	set mng3;
	eventdat=anndats;
	format eventdat yymmddn8.;
	keep permno eventdat;
run;

proc sort data=events nodupkey; by permno eventdat; run;


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

/*market model CARS*/
rsubmit;
options fullstimer ps=60;
 
proc upload data=events out=events; run;

eventus;  
request insas=events autodate Est=-11 estlen=200 Minestn=180 ;
windows (-1,1) (-2,2) (-1,3), (0,0), (0,1);
evtstudy pre=1 post=1 both 
outwin=work.carmm nonames noprint;
 
proc download data=carmm; run;                                                     
endrsubmit;       

/*fama french w/ momentum model CARS*/
rsubmit;
options fullstimer ps=60;
 
proc upload data=events out=events; run;

eventus fff=ff.factors_daily;  
request insas=events autodate Est=-11 estlen=200 Minestn=180 ;
windows (-1,1) (-2,2) (-1,3), (0,0), (0,1);
evtstudy pre=1 post=1 both famafrench momentum twostep
outwin=work.carffm nonames noprint;
 
proc download data=carffm; run;                                                     
endrsubmit;       


/*buy and hold return*/
rsubmit;
options fullstimer ps=60;
 
proc upload data=events out=events; run;

eventus;  
request insas=events autodate;
windows (-210,-11);
evtstudy mar both buyhold overlap
outwin=work.bhar nonames noprint;
 
proc download data=bhar; run;                                                     
endrsubmit;       


/*merge everything back*/
proc sql;
	create table mng4 as select a.*, b.car_window_1 as carmm3, b.car_window_2 as carmm5,
	b.car_window_3 as carmm4, b.car_window_4 as carmm1, b.car_window_5 as carmm2
	from mng3 as a left join carmm as b
	on a.permno=b.permno and a.anndats=b.original_eventdat and (b._weight_="Equal");
quit;

proc sql;
	create table mng4 as select a.*, b.car_window_1 as carmmv3, b.car_window_2 as carmmv5,
	b.car_window_3 as carmmv4, b.car_window_4 as carmmv1, b.car_window_5 as carmmv2
	from mng4 as a left join carmm as b
	on a.permno=b.permno and a.anndats=b.original_eventdat and (b._weight_="Value");
quit;

proc sql;
	create table mng4 as select a.*, b.car_window_1 as carffm3, b.car_window_2 as carffm5,
	b.car_window_3 as carffm4, b.car_window_4 as carffm1, b.car_window_5 as carffm2
	from mng4 as a left join carffm as b
	on a.permno=b.permno and a.anndats=b.original_eventdat and (b._weight_="Equal");
quit;

proc sql;
	create table mng4 as select a.*, b.car_window_1 as carffmv3, b.car_window_2 as carffmv5,
	b.car_window_3 as carffmv4, b.car_window_4 as carffmv1, b.car_window_5 as carffmv2
	from mng4 as a left join carffm as b
	on a.permno=b.permno and a.anndats=b.original_eventdat and (b._weight_="Value");
quit;

proc sql;
	create table mng4 as select a.*, b.bhar_window_1 as bhar
	from mng4 as a left join bhar as b
	on a.permno=b.permno and a.anndats=b.original_eventdat and (b._weight_="Equal")
	and (b.restype="MAR");
quit;

proc sql;
	create table mng4 as select a.*, b.bhar_window_1 as bharv
	from mng4 as a left join bhar as b
	on a.permno=b.permno and a.anndats=b.original_eventdat and (b._weight_="Value")
	and (b.restype="MAR");
quit;

proc means data=mng4; run;

/*determmine if forecast is bundled w/ earnings announcement*/

data tday; set m.tday; run;

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

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

data compq;		
	set m.fundq;
	if rdq ne .;
	keep gvkeyn fyearq fqtr datadate RDQ;
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;

proc sql;
	create table mng6 as select a.*, b.rdq
	from mng5 as a left join compq1 as b
	on a.gvkeyn=b.gvkeyn and (a.tday-2)<=b.tday<=(a.tday+2);
quit;

proc sql;
	create table mng6 as select *, count(rdq) as bundled
	from mng6
	group by id;
quit;

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

/*proc freq data=mng6; table bundled; run;*/


/*****************************************************************/
/*create compustat variables*/
/*****************************************************************/
data comp;
	set m.funda;
	if fyear ne . and gvkeyn ne .;
	keep fyear gvkeyn datadate sich;
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; 
	keep gvkeyn sicn;
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 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 prccq cshoq ibq niq atq ceqq oibdpq;
run;

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

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

proc sql;
	create table mng7 as select a.*, b.*, a.anndats-b.datadate as diffc
	from mng6 as a, comp2 as b
	where a.gvkeyn=b.gvkeyn and (a.anndats-120)<=b.datadate<a.anndats;
quit;

proc sort data=mng7; by id diffc; run;
proc sort data=mng7 nodupkey; by id; run;

/*calculate earnings surprise*/

data ibes; set m.statsumu_epsus; run;

data mng7;
	set mng7;
	nmo = (year(anndats)-2000)*12+month(anndats);
run;

data act; 
	set m.actu_epsus;
	if measure="EPS";
run;

proc sql;
	create table ibes1 as select a.*, b.value as actual, b.anndats as actanndats
	from ibes as a inner join act as b
	on a.ticker=b.ticker and a.cusip=b.cusip and a.fpedats=b.pends and a.fiscalp=b.pdicity;
quit;

data ibes2;
	set ibes1;
	if actual ne .;
	if statpers<actanndats;
	if fiscalp="QTR";
run;

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; run;

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

proc sort data=ibes2 nodupkey; by permno fpedats descending statpers; run;
proc sort data=ibes2 nodupkey; by permno fpedats; run;

data crsp;
	set m.dsf;
	if year(date)>=1995;
	prc=abs(prc);
	keep permno date cfacshr cfacpr prc;
run;

proc sql;
	create table ibes2a as select a.*, b.cfacshr, a.actanndats-b.date as diff
	from ibes2 as a inner join crsp as b
	on a.permno=b.permno and (a.actanndats-5)<=b.date<=(a.actanndats);
quit;

proc sort data=ibes2a; by permno fpedats diff; run;
proc sort data=ibes2a nodupkey; by permno fpedats; run;

proc sql;
	create table ibes2a as select a.*, b.cfacshr as cfacshr1, a.statpers-b.date as diff1
	from ibes2a as a inner join crsp as b
	on a.permno=b.permno and (a.statpers-5)<=b.date<=(a.statpers);
quit;

proc sort data=ibes2a; by permno fpedats diff1; run;
proc sort data=ibes2a nodupkey; by permno fpedats; run;

data ibes2b;
	set ibes2a;
	actual=actual/cfacshr;
	medest=medest/cfacshr1;
	meanest=meanest/cfacshr1;
	stdev=stdev/cfacshr1;
	esurp = (actual-medest)/abs(medest);
	esurp1 = (actual-meanest)/abs(meanest);
run;

data m.eps_surprise; set ibes2b; run;

proc sql;
	create table mng8 as select a.*, b.esurp, b.esurp1, b.medest as medest0, b.meanest as meanest0,
	b.numest as numest0, b.stdev as stdev0, a.anndats-b.fpedats as  diffi
	from mng7 as a left join ibes2b as b
	on a.permno=b.permno and (a.anndats-366)<=b.fpedats<=a.anndats; 
quit;

proc sort data=mng8; by id diffi; run;
proc sort data=mng8 nodupkey; by id; run;

/*proc means data=mng8; var diffi; run;*/


proc sort data=mng8 out=mng9 nodupkey; by permno anndats fpe pdicity mngfor score; run;
proc sort data=mng9 nodupkey; by permno anndats fpe pdicity mngfor; run;

proc means data=mng9; run;


proc export data=mng9
	outfile= "Path\Management Forecast News JAR.dta" 
	replace;
run; 
 


/*****************************************************************/
/*Create bundled forecast adjustment*/
/*****************************************************************/
data tday; set m.tday; run;
data ibes5; set m.ibes5; run;

data comp;
	set m.funda;
	if fyear ne . and gvkeyn ne .;
	if fyear>=1985;
	keep fyear gvkeyn datadate sich;
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; 
	keep gvkeyn sicn;
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;

/*calculate variables used to estimate likelihood forecast is bundled. Eq b.1 in Appendix A*/

data compq;		
	set m.fundq;
	if fyearq>=1985;
	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;
	if rdq ne .;
	keep gvkeyn fyearq fqtr datadate mbe mb blev cfa oroa mve rdq;
run;

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

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

data compq1;
	set compq;
	qobs = (year(datadate)-2000)*4+fqtr;
run;

proc sql;
	create table compq1 as select a.*, b.tday, b.date-a.rdq as diff
	from compq1 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;

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

proc sql;
	create table compq2 as select a.*, b.anndats
	from compq1 as a left join for as b
	on a.gvkeyn=b.gvkeyn and (a.tday-2)<=b.tday<=(a.tday+2);
quit;

proc sql;
	create table compq2 as select *, count(anndats) as bundled
	from compq2
	group by gvkeyn, qobs;
quit;

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

proc sql;
	create table compq2 as select distinct a.*, max(b.mngfor) as mfissued
	from compq2 as a left join for as b
	on a.gvkeyn=b.gvkeyn and (a.datadate-5)<=b.fpe<=(a.datadate+5)
	group by a.gvkeyn, a.qobs;
quit;

data compq2;
	set compq2;
	if mfissued=. then mfissued=0; if mfissued ne 0 then mfissued=1;
	if bundled>0 then bundled=1; else bundled=0;
run;

proc sql;
	create table compq3 as select a.*, b.bundled as prebundled
	from compq2 as a, compq2 as b
	where a.gvkeyn=b.gvkeyn and a.qobs=b.qobs+1;
quit;

data ibess; 
	set m.statsum_epsus;
	nmo = (year(statpers)-2000)*12+month(statpers);
	if year(statpers)>=1990;
	keep ticker actual fpi fiscalp nmo statpers fpedats numest stdev medest meanest;
run;

proc sort data=ibess out=ibess1 nodupkey; by ticker statpers fiscalp fpi fpedats; run;

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);
	keep permno date;
run;

proc sql;
	create table ibess2 as select a.*, b.permno
	from ibess1 as a, link as b, crspm as c
	where a.ticker=b.ticker and b.permno=c.permno 
	and intnx('month',a.statpers,0,'E') = intnx('month',c.date,0,'E');
quit;

proc sort data=ibess2 nodupkey; by ticker statpers fiscalp fpedats; run;

data link;	set m.CCMXPF_LINKTABLE;	run;

data link1;
	set link;
	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 sql;
	create table ibess2 as select a.*, b.gvkeyn
	from ibess2 as a inner join link1 as b
	on a.permno=b.permno and b.linkdt<=a.statpers<=b.linkenddt;
quit;

proc sort data=ibess2 nodupkey; by ticker statpers fiscalp fpedats; run;

data compq3;
	set compq3;
	nmo = (year(rdq)-2000)*12+month(rdq);
run;

data ibess2;
	set ibess2;
	*if fiscalp="QTR";
	if actual ne .;
run;

proc sql;
	create table compq4 as select a.*, b.permno, b.numest, b.stdev, b.actual, b.meanest, b.fiscalp,
	b.medest
	from compq3 as a left join ibess2 as b
	on a.gvkeyn=b.gvkeyn and a.nmo=b.nmo+1  
	and year(a.datadate)=year(b.fpedats) and month(a.datadate)=month(b.fpedats);
quit;

proc sort data=compq4 nodupkey; by gvkeyn qobs fiscalp; run;

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

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

proc sql;
	create table compq5 as select a.*, b.prc/b.cfacpr as prc, b.mve as mve1
	from compq4 as a left join crspb1 as b
	on a.permno=b.permno and a.tday=b.tday+1;
quit;

proc sql;
	create table compq5 as select a.*, b.sich
	from compq5 as a left join comp2 as b
	on a.gvkeyn=b.gvkeyn and a.fyearq=b.fyear;
quit;

proc sort data=compq5 nodupkey; by gvkeyn qobs fiscalp; run;

data compq6;
	set compq5;
	SURP = (actual-meanest)/prc;
	absSURP = abs(surp);
	GNUE=0; if surp>.0001 then GNUE=1;
	BNUE=0; if surp<-.0001 then BNUE=1;
	SURPm = (actual-medest)/prc;
	absSURPm = abs(surpm);
	GNUEm=0; if surpm>.0001 then GNUEm=1;
	BNUEm=0; if surpm<-.0001 then BNUEm=1;
	DISP = stdev;
	LNMVE = log(mve1);
	LnNUMEST = log(1+numest);
	sic2 = substr(sich,9,2)*1;
	year = year(rdq);
	beat = 0; if surp>=0 then beat=1;
	if surp ne . and disp ne . and lnmve ne . and lnnumest ne . and sic2 ne . and year ne .
	and lnmve ne .;
	if fiscalp="QTR";
	drop mbe;
run;

proc sql;
	create table compq7 as select distinct a.*, exp(sum(log(1+b.ret)))-1 as fret, 
	count(b.permno) as robs
	from compq6 as a, crspb1 as b
	where a.permno=b.permno and a.rdq-90<=b.date<a.rdq
	group by a.permno, a.qobs, a.fiscalp;
quit;

proc sql;
	create table compq7 as select distinct a.*, avg(b.beat) as MBE, count(b.gvkeyn) as obs
	from compq7 as a, compq6 as b
	where a.gvkeyn=b.gvkeyn and (a.qobs-4)<=b.qobs<a.qobs
	group by a.gvkeyn, a.qobs, a.fiscalp;
quit;

proc sort data=compq7 nodupkey; by gvkeyn qobs fiscalp; run;

options msglevel=i; %include 'Path\macros.sas';
%WT(data = compq7, out=compq7, byvar=none, 
	vars =  abssurp abssurpm fret disp lnmve lnnumest,
		type = W, pctl = 1 99, drop=N);
run;

data compq7;
	set compq7;
	loss=0; if actual<0 then loss=1;
run;

proc means data=compq7; run;

proc surveylogistic data=compq7;
	class sic2 year;
	model bundled (event='1') = mfissued prebundled gnue bnue abssurp loss disp fret lnmve lnnumest
				mbe sic2 year / link=probit rsquare;
	output out=compq8 predicted=pbundled;
quit;

proc surveylogistic data=compq8;
	class sic2 year;
	model bundled (event='1') = mfissued prebundled gnuem bnuem abssurpm loss disp fret lnmve lnnumest
				mbe sic2 year / link=probit rsquare;
	output out=compq8 predicted=pbundledm;
quit;                    

/*calculate variables used to estimate analyst forecast revision. Eq b.2 in Appendix A*/

proc sql;
	create table temp as select distinct a.permno, a.rdq, a.prc, b.value/b.cfacshr as value, b.eid, 
	b.estimator, b.analys, b.anndats, b.astpdats, b.excdats, b.fpedats, b.pdicity, b.fpi
	from compq8 as a inner join ibes5 as b
	on a.permno=b.permno and (a.rdq-30)<=b.anndats<a.rdq
	order by a.permno, a.rdq, b.anndats;
quit;

data temp;
	set temp;
	if astpdats ne . and astpdats<anndats then delete;
	if excdats ne . and excdats<anndats then delete;
run;

proc sort data=temp;
	by permno rdq fpedats fpi pdicity estimator anndats;
run;

data temp;
	set temp;
	by permno rdq fpedats fpi pdicity estimator anndats;
	if last.estimator;
run;

proc means data=temp noprint;
	by permno rdq fpedats fpi pdicity;
	var value;
	output out=temp1 mean=meanest median=medest n=numest std=stddev;
quit;

proc sql;
	create table temp2 as select distinct a.permno, a.rdq, a.prc, b.value/b.cfacshr as value, b.eid, 
	b.estimator, b.analys, b.anndats, b.astpdats, b.excdats, b.fpedats, b.pdicity, b.fpi
	from compq8 as a inner join ibes5 as b
	on a.permno=b.permno and (a.rdq-30)<=b.anndats<=a.rdq+5
	order by a.permno, a.rdq, b.anndats;
quit;

data temp2;
	set temp2;
	if astpdats ne . and astpdats<anndats then delete;
	if excdats ne . and excdats<anndats then delete;
run;

proc sort data=temp2;
	by permno rdq fpedats fpi pdicity estimator anndats;
run;

data temp2;
	set temp2;
	by permno rdq fpedats fpi pdicity estimator anndats;
	if last.estimator;
run;

proc means data=temp2 noprint;
	by permno rdq fpedats fpi pdicity;
	var value;
	output out=temp3 mean=meanest median=medest n=numest std=stddev;
quit;

proc sql;
	create table compq9 as select a.*, b.meanest as meanest0, b.medest as medest0, b.fpedats, b.fpi,
	b.pdicity
	from compq8 as a left join temp1 as b
	on a.permno=b.permno and a.rdq=b.rdq;
quit;

/*ods html close; ods html;*/

proc sql;
	create table compq9 as select a.*, b.meanest as meanest1, b.medest as medest1
	from compq9 as a left join temp3 as b
	on a.permno=b.permno and a.rdq=b.rdq and a.fpi=b.fpi and a.fpedats=b.fpedats
	and a.pdicity=b.pdicity;
quit;

proc sort data=compq9 nodupkey; by permno rdq pdicity fpedats fpi; run;

data compq9;
	set compq9;
	AREV = (meanest1-meanest0)/prc;
	AREVm = (medest1-medest0)/prc;
	EP = actual/prc;
	if fpedats ne .;
run;

options msglevel=i; %include 'Path\macros.sas';
%WT(data = compq9, out=compq9, byvar=none, 
	vars =  arev arevm surp surpm,
		type = W, pctl = 1 99, drop=N);
run;

proc rank data=compq9 out=compq10 groups=10 ties=low;
	var lnmve ep;
	ranks rlnmve rep;
run;

data compq10;
	set compq10;
	ahead = fpedats-datadate;
	if 0<=ahead<=85 then head=1;
	if 85<=ahead<=177 then head=2;
	if 178<=ahead<=269 then head=3;
	if 270<=ahead<=361 then head=4;
	if 362<=ahead<=453 then head=5;
	if 454<=ahead<=545 then head=6;
	if 546<=ahead<=637 then head=7;
	if 638<=ahead<=729 then head=8;
	if 730<=ahead<=820 then head=9;
	if 821<=ahead<=911 then head=10;
	if head ne .;
	gnue_surp=gnue*surp;
	bnue_surp=bnue*surp;
	surp_abssurp=surp*abssurp;
	surp_rlnmve=surp*rlnmve;
	surp_rep=surp*rep;
	gnue_surpm=gnue*surpm;
	bnue_surpm=bnue*surpm;
	surp_abssurpm=surpm*abssurp;
	surp_rlnmvem=surpm*rlnmve;
	surp_repm=surpm*rep;
run;

proc means data=compq10; run;

proc sort data=compq10; by year pdicity head; run;

proc reg data=compq10 noprint outest=beta;
	where 1997<=year<=2017 and bundled=0;
	by year pdicity head;
	model arev = gnue bnue gnue_surp bnue_surp fret surp_abssurp 
			surp_rlnmve surp_rep pbundled / rsquare;
quit;

proc sort data=beta; by pdicity head; run;

proc sort data=compq10 out=temp nodupkey; by fqtr pdicity fpi; run;

proc means data=beta mean probt;
	by pdicity head;
quit;

proc sql;
	create table compq11 as select a.*, b.intercept+a.gnue*b.gnue+a.bnue*b.bnue
	+a.gnue_surp*b.gnue_surp+a.bnue_surp*b.bnue_surp+a.fret*b.fret+a.surp_abssurp*b.surp_abssurp 
	+a.surp_rlnmve*b.surp_rlnmve+a.surp_rep*b.surp_rep+a.pbundled*b.pbundled as pafrev
	from compq10 as a inner join beta as b
	on a.year=b.year and a.pdicity=b.pdicity and a.head=b.head;
quit;

proc reg data=compq10 noprint outest=beta;
	where 1997<=year<=2017 and bundled=0;
	by year pdicity head;
	model arevm = gnuem bnuem gnue_surpm bnue_surpm fret surp_abssurpm 
			surp_rlnmvem surp_repm pbundledm / rsquare;
quit;

/*calculate predicted analyst forecast revision and merge back to sample*/

proc sql;
	create table compq11 as select a.*, b.intercept+a.gnuem*b.gnuem+a.bnuem*b.bnuem
	+a.gnue_surpm*b.gnue_surpm+a.bnue_surpm*b.bnue_surpm+a.fret*b.fret+a.surp_abssurpm*b.surp_abssurpm 
	+a.surp_rlnmvem*b.surp_rlnmvem+a.surp_repm*b.surp_repm+a.pbundledm*b.pbundledm as pafrevm
	from compq11 as a inner join beta as b
	on a.year=b.year and a.pdicity=b.pdicity and a.head=b.head;
quit;

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

/*proc freq data=mng; table year; run;*/

proc sql;
	create table mng1 as select a.*, b.pafrev*b.prc as revadj, b.pafrevm*b.prc as revadjm
	from mng as a left join compq11 as b
	on a.permno=b.permno and (b.tday-2)<=a.tday<=(b.tday+2) and a.fpe=b.fpedats
	and a.pdicitys=b.pdicity;
quit;

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

/*calculate recession and other macro variables*/

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

proc import datafile="Path\US GDP 2-26-2019.xlsx"
	replace out=gdp dbms=xlsx;
	getnames=yes;
	sheet="Clean";
quit;

data gdp;
	set gdp;
	qnmo = (year-2000)*4+qtr;
run;

data mng3;
	set mng3;
	y=year(anndats); m=month(anndats);
	if m in (1,2,3) then qtr1=1;
	if m in (4,5,6) then qtr1=2;
	if m in (7,8,9) then qtr1=3;
	if m in (10,11,12) then qtr1=4;
	qnmo1 = (y-2000)*4+qtr1;
run;

proc sql;
	create table mng3 as select a.*, b.pgdp as pgdp1
	from mng3 as a left join gdp as b
	on a.qnmo1=b.qnmo;
quit;

/*calculate remaining variables used in regressions*/

proc sort data=mng3 out=mng4 nodupkey; by permno anndats fpe pdicity mngfor; run;

proc sql;
	create table mng4 as select *, count(permno) as nsameday
	from mng4
	group by permno, anndats;
quit;

proc freq data=mng4; table year; run;

/*institutional 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 decending 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>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;

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

proc sort data=mng4a; by id ndiff; run;
proc sort data=mng4a nodupkey; by id; run;


/*monthly return volatility*/

data crspm;
	set m.msf;
	if year(date)>=1990;
	nmo = (year(date)-2000)*12+month(date);
	keep permno date ret nmo;
run;

data mng4a;
	set mng4a;
	nmod = (year(datadate)-2000)*12+month(datadate);
run;

proc sql;
	create table temp1 as select distinct a.id, std(b.ret)*sqrt(12) as rvol1, count(b.ret) as obs
	from mng4a as a, crspm as b
	where a.permno=b.permno and (a.nmod-11)<=b.nmo<=a.nmod
	group by a.id;
quit;

proc sql;
	create table mng4a as select a.*, b.rvol1
	from mng4a as a left join temp1 as b
	on a.id=b.id;
quit;

/*raw announcement returns*/

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

proc sort data=mng2 nodupkey out=mng2; by id; run;

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

proc sql;
	create table temp1 as select distinct a.id, sum(b.ret) as car3, count(b.ret) as obs
	from mng4a as a, crspd1 as b
	where a.permno=b.permno and (a.tday-1)<=b.tday<=(a.tday+1)
	group by id;
quit;

proc sql;
	create table mng4a as select a.*, b.car3
	from mng4a as a left join temp1 as b
	on a.id=b.id;
quit;

proc sql;
	create table temp1 as select distinct a.id, sum(b.ret) as car2, count(b.ret) as obs
	from mng4a as a, crspd1 as b
	where a.permno=b.permno and (a.tday-0)<=b.tday<=(a.tday+1)
	group by id;
quit;

proc sql;
	create table mng4a as select a.*, b.car2
	from mng4a as a left join temp1 as b
	on a.id=b.id;
quit;

proc sql;
	create table temp1 as select distinct a.id, sum(b.ret) as car1, count(b.ret) as obs,
	b.shrout*b.cfacshr as shareadj
	from mng4a as a, crspd1 as b
	where a.permno=b.permno and (a.tday-0)<=b.tday<=(a.tday+0)
	group by id;
quit;

proc sql;
	create table mng4a as select a.*, b.car1, b.shareadj
	from mng4a as a left join temp1 as b
	on a.id=b.id;
quit;

data index; set m.dsi; run;

proc sql;
	create table crspd1 as select a.*, b.vwretd, b.ewretd
	from crspd1 as a left join index as b
	on a.date=b.date;
quit;

/*alternative buy and hold return and price measures*/

proc sql;
	create table mng4a as select distinct a.*, 
	exp(sum(log(1+b.ret)))-exp(sum(log(1+b.vwretd))) as bhar20, count(b.ret) as obs20
	from mng4a as a left join crspd1 as b
	on a.permno=b.permno and (a.tday-21)<=b.tday<=(a.tday-2)
	group by a.id;
quit;

proc sql;
	create table mng4b as select a.*, b.prc/b.cfacpr as prcq, b.mve as mveq, b.date-a.datadate as diffc1
	from mng4a as a left join crspd1 as b
	on a.permno=b.permno and (a.datadate)<=b.date<=(a.datadate+4);
quit;

proc sort data=mng4b; by id diffc1; run;
proc sort data=mng4b nodupkey; by id; run;

/*create remaining variables and clean sample*/

data mng5;
	set mng4b;
	if bundled>1 then bundled=1;
	if fpe ne . and anndats ne .;
	if recession=. then recession=0;
	if pgdp1 ne . then dtgdp1=0; if pgdp1 ne . and pgdp1<0 then dtgdp1=1;

	lnmve = log(mve/cpi);
	lnanal = log(numest30);
	lndisp = log(1+stddev30);
	disp = stddev30/abs(meanest30);
	horizon = (fpe-anndats)/365;
	if horizon>=0;
	year = year(anndats);
	if io=. then io=0;
	if io>1 then io=1;
	if 2002<=year<=2017;
	if measure="EPS";
	if pdicity in ("ANN","QTR");

	/*apply bundled forecast adjustments*/

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

	news2 = (mngfor-medest30)/stddev30;
	if bundled=1 then news2 = (mngfor-(medest30+revadjm))/stddev30;

	news3 = (mngfor-medest30)/prcq;
	if bundled=1 then news3 = (mngfor-(medest30+revadjm))/prcq;

	earnings=medest30*shareadj;
	valueadj = mngfor*shareadj;
	if ceqq>0 then news4 = (valueadj-earnings)/(ceqq*1000);
	if ceqq>0 and bundled=1 then news4 = (valueadj-(earnings+revadjm*shareadj))/(ceqq*1000);

	if mngfor ne . and medest30 ne .;
	if lnmve ne . and blev ne . and oroa ne . and mbe ne . and esurp ne . and lnanal ne .
	and bhar20 ne . and horizon ne . and rvol1 ne . ;
	if carmmv1 ne .;
	if news1 ne .;
	if shrcd in (10,11);

	range=0; if score=2 then range=1;
	qtrfor=0; if pdicity="QTR" then qtrfor=1;
	if range=1 then widthm=abs(val_2-val_1)/abs(medest30);
	if range=1 and bundled=1 then widthm=abs(val_2-val_1)/abs(medest30+revadjm);
	if widthm=. then widthm=0;
run; 

proc datasets lib=work;
	modify mng5;
	rename PERMNO=permno;
quit;

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

proc sql;
	create table mng5 as select *, count(id) as fobs
	from mng5
	group by permno, bundled;
quit;

data mng5; set mng5; if fobs>1; run;

proc sql;
	create table mng5 as select *, count(id) as tobs
	from mng5
	group by anndats, bundled;
quit;

data mng5; set mng5; if tobs>1; run;

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

proc sql;
	create table mng5 as select *, count(id) as fobs1
	from mng5
	group by permno, bundled;
quit;

data mng5; set mng5; if fobs1>1; run;

proc sql;
	create table mng5 as select *, count(id) as tobs1
	from mng5
	group by anndats, bundled;
quit;

data mng5; set mng5; if tobs1>1; run;

/*number of other same day forecasts and whether forecast is first one for a fiscal period end*/

proc sql;
	create table mng5 as select *, count(permno) as nsameday1
	from mng5
	group by permno, anndats;
quit;

proc sort data=mng5; by permno fpe anndats; run;

data mng5;
	set mng5;
	count+1;
	by permno fpe;
	if first.permno or first.fpe then count=1;
	order=count;
	first=0; if order=1 then first=1;

	drop fobs fobs1 tobs tobs1;
run;

/*winsorize*/

options msglevel=i; %include 'Path\macros.sas';
%WT(data = mng5, out=mng5, byvar=none, 
	vars =  carmm3 carmm4 carmm2 carmm1 carmm5 carmmv3 carmmv4 carmmv2 carmmv1 carmmv5
	carffm3 carffm4 carffm5 carffmv3 carffmv4 carffmv5 carffm2 carffm1 carffmv2 carffmv1 car1 car2 car3
	news1 news2 news3 news4 blev cfa oroa lnmve mb mbe lnanal esurp esurp1 rvol1 
	horizon bhar bhar20 bharv lndisp disp nsameday1 widthm ,
		type = W, pctl = 1 99, drop=N);
run;

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

proc means data=mng5; run;

proc export data=mng5
	outfile= "Path\Management Forecast News JAR1.dta" 
	replace;
run; 





/*****************************************************************************************/
/*create sample and variables for tests examining analyst forecast revisions to mng forecasts*/
/*****************************************************************************************/

libname m 'Path'; run;
data ibes5; set m.ibes5; run;

data exc; set m.excu_epsus; run;
data stop; set m.stopu_epsus; run;

data ibes1;
	set ibes;
	if fpi in (1,2,3,4,5) then pdicity="A";
	if fpi in (6,7,8,9) then pdicity="Q";
	if pdicity in ("A", "Q");
	if ticker ne "" andf anndats ne . and fpedats ne .;
run;

data stop1;
	set stop;
	if measure="EPS";
	stop=1;
run;

proc sql;
	create table ibes2 as select a.*, b.astpdats
	from ibes1 as a left join stop1 as b
	on a.ticker=b.ticker and a.fpedats=b.fpedats and a.estimator=b.estimator
	and a.pdicity=b.pdicity and b.astpdats>a.actdats;
quit;

data exc1;
	set exc;
	if measure="EPS";
	exc=1;
	if fpi in (1,2,3,4,5) then pdicity="A";
	if fpi in (6,7,8,9) then pdicity="Q";
run;

proc sql;
	create table ibes2 as select a.*, b.excdats
	from ibes2 as a left join exc1 as b
	on a.ticker=b.ticker and a.fpedats=b.fpedats and a.estimator=b.estimator
	and a.pdicity=b.pdicity and a.actdats=b.actdats and a.acttims=b.acttims;
quit;

proc sort data=ibes2 out=ibes3 nodupkey;
	by ticker fpedats estimator analys actdats acttims;
run;

data ibes3;
	set ibes3;
	EID = _n_;
run;


/*add permno identifiers*/

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 ibes4 as select a.*, b.permno, b.score as ibescore
	from ibes3 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 sort data=ibes4 nodupkey; by eid; run;

data crsp;
	set m.dsf;
	if year(date)>=1990;
	keep permno date cfacshr;
run;

proc sql;
	create table ibes5 as select a.*, b.cfacshr, a.anndats-b.date as diff
	from ibes4 as a left join crsp as b
	on a.permno=b.permno and (a.anndats-4)<=b.date<=(a.anndats+0);
quit;

proc sort data=ibes5; by eid diff; run;
proc sort data=ibes5 nodupkey; by eid; run;


proc import datafile="Path\Management Forecast News JAR1.dta" 
	replace out=mng;
run; 

data mng;
	set mng;
	ceqq0=ceqq;
	if ceqq<0 then ceqq0=.;
run;

/*determine an analyst's last (first) forecast before (after) a management forecast*/

proc sql;
	create table temp as select a.*, b.value, b.cfacshr, b.eid, b.estimator, b.analys, 
	b.anndats as ianndats, b.astpdats, b.excdats, b.anntims as ianntims
	from mng as a inner join ibes5 as b
	on a.ticker=b.ticker and a.pdicitys=b.pdicity and a.fpe=b.fpedats 
	and (a.anndatso-30)<=b.anndats<=(a.anndatso+0)
	order by a.id, b.anndats;
quit;

data temp;
	set temp;
	if analys ne 0;
	value = value/cfacshr;
	if astpdats ne . and astpdats<anndatso then delete;
	if excdats ne . and excdats<anndatso then delete;
	if anndatso=ianndats then delete;
run;

proc sort data=temp;
	by id analys ianndats ianntims;
run;

data temp;
	set temp;
	by id analys ianndats ianntims;
	if last.analys;
run;

proc sql;
	create table temp1 as select a.*, b.value, b.cfacshr, b.eid, b.estimator, b.analys, 
	b.anndats as ianndats, b.astpdats, b.excdats, b.anntims as ianntims
	from mng as a inner join ibes5 as b
	on a.ticker=b.ticker and a.pdicitys=b.pdicity and a.fpe=b.fpedats 
	and (a.anndatso)<=b.anndats<=(a.anndatso+30)
	order by a.id, b.anndats;
quit;

data temp1;
	set temp1;
	if analys ne 0;
	value = value/cfacshr;
	if astpdats ne . and astpdats<anndatso then delete;
	if excdats ne . and excdats<anndatso then delete;
	if anndatso=ianndats then delete;
run;

proc sort data=temp1;
	by id analys ianndats ianntims;
run;

data temp1;
	set temp1;
	by id analys ianndats ianntims;
	if first.analys;
	time = ianndats-anndatso;
run;

/*calculate revision variables*/

proc sql;
	create table temp2 as select a.*, (b.value-a.value)/abs(a.value) as afrev,
	(a.mngfor-a.value)/abs(a.value) as newsa,
	(b.value*a.shareadj-a.value*a.shareadj)/(a.ceqq0*1000) as afrev4,
	(a.mngfor*a.shareadj-a.value*a.shareadj)/(a.ceqq0*1000) as newsa4,
	b.time, b.value as rvalue
	from temp as a left join temp1 as b
	on a.id=b.id and a.analys=b.analys;
quit;

data temp2;
	set temp2;
	if time=. then time=30;
	if afrev=. then afrev=0;
	if afrev4=. then afrev4=0;
	if value=0 then do; newsa=.; afrev=.; end;
run;

proc sort data=temp2 nodupkey; by id analys; run;

/*winsorize*/

options msglevel=i; %include 'Path\macros.sas';
%WT(data = temp2, out=temp2, byvar=none, 
	vars =  afrev newsa afrev4 newsa4,
		type = W, pctl = 1 99, drop=N);
run;

proc corr data=temp2;
	var afrev newsa afrev4 newsa4;
run;

proc means data=temp2;
	where rvalue ne .;
	var time;
run;

proc datasets lib=work;
	modify temp2;
	rename ANALYS=analys;
quit;

proc export data=temp2
	outfile= "Path\Analyst Revisions JAR.dta" 
	replace;
run; 



/*******************************************************************************/
/*calculate analyst and management forecast accuracy*/
/*******************************************************************************/

proc import datafile="Path\Management Forecast News JAR1.dta" 
	replace out=mng;
run; 

data mng;
	set mng;
	keep ticker permno fpe pdicity anndats medest30 prcq mngfor id stddev30
	revadjm bundled ceqq atq shareadj;
run;

libname m 'Path'; run;

data act; 
	set m.actu_epsus;
	if measure="EPS";
run;

proc sql;
	create table mng1 as select a.*, b.value as actual, b.anndats as actanndats
	from mng as a left join act as b
	on a.ticker=b.ticker and a.fpe=b.pends and a.pdicity=b.pdicity;
quit;

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

data crsp;
	set m.dsf;
	if year(date)>=1990;
	prc=abs(prc);
	keep permno date cfacshr cfacpr prc;
run;

proc sql;
	create table mng2 as select a.*, b.cfacshr, a.actanndats-b.date as diff
	from mng1 as a left join crsp as b
	on a.permno=b.permno and (a.actanndats-5)<=b.date<=(a.actanndats);
quit;

proc sort data=mng2; where diff>=0; by id diff; run;
proc sort data=mng2 nodupkey; by id; run;

data mng3;
	set mng2;
	absmngacc1 = abs((mngfor-actual/cfacshr))/abs(mean(mngfor,medest30));
	if bundled=1 then absmngacc1 = abs((mngfor-actual/cfacshr))/abs(mean(mngfor,medest30+revadjm));
	absmngacc2 = abs((mngfor*shareadj-actual/cfacshr*shareadj))/(atq*1000);
	if ceqq>0 then absmngacc2 = abs((mngfor*shareadj-actual/cfacshr*shareadj))/(ceqq*1000);

	absanacc1 = abs((medest30-actual/cfacshr))/abs(mean(mngfor,medest30));
	if bundled=1 then absanacc1 = abs(((medest30+revadjm)-actual/cfacshr))/abs(mean(mngfor,medest30+revadjm));
	if ceqq>0 then absanacc2 = abs((medest30*shareadj-actual/cfacshr*shareadj))/(ceqq*1000);
	if bundled=1 and ceqq>0 then absanacc2 = abs(((medest30+revadjm)*shareadj-actual/cfacshr*shareadj))/(ceqq*1000);

	diffacc1 = absmngacc1-absanacc1;
	greater1=0;
	if diffacc1>0 then greater1=1;
	if diffacc1 ne .;

	diffacc2 = absmngacc2-absanacc2;
	
	drop diff cfacshr cfacpr;
run;

/*add forecast accuracy measure (used in paper) to main mng forecast sample*/

proc import datafile="Path\Management Forecast News JAR1.dta" 
	replace out=sample;
run; 

proc sql;
	create table sample1 as select distinct a.*, b.diffacc1, b.diffacc2, b.greater1
	from sample as a left join mng3 as b
	on a.id=b.id;
quit;

/*winsorize*/

options msglevel=i; %include 'Path\macros.sas';
%WT(data = sample1, out=sample2, byvar=none, 
	vars = diffacc1 diffacc2,
		type = W, pctl = 1 99, drop=N);
run;

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

proc means data=sample2; var diffacc1 diffacc2 greater1; run;

proc export data=sample2
	outfile= "Path\Management Forecast News JAR1.dta" 
	replace;
run; 


/**********************************************************************/
/*add variables related to price drift and reversals*/
/**********************************************************************/

proc import datafile= "Path\Management Forecast News JAR1.dta" 
	replace out=mng;
run; 

libname m 'Path'; run;
data crspd1;
	set m.dsf;
	if year(date)>=1995;
	prc=abs(prc);
	keep permno date shrout vol prc ret;
run;

data ff; set m.factors_daily; run;
data index;	set m.dsi; run;
data tday; set m.tday; run;

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

proc sql;
	create table crspd1a as select a.*, b.vwretd
	from crspd1a as a left join index as b
	on a.date=b.date;
quit;

proc sql;
	create table temp as select distinct a.id, exp(sum(log(1+b.ret)))-exp(sum(log(1+b.vwretd))) as post7v
	from mng as a, crspd1a as b
	where a.permno=b.permno and (a.tday+2)<=b.tday<(a.tday+7)
	group by id;
quit;

proc sql;
	create table mng1 as select a.*, b.post7v
	from mng as a left join temp as b
	on a.id=b.id;
quit;

proc sql;
	create table temp as select distinct a.id, exp(sum(log(1+b.ret)))-exp(sum(log(1+b.vwretd))) as post30v
	from mng as a, crspd1a as b
	where a.permno=b.permno and (a.tday+2)<=b.tday<(a.tday+22)
	group by id;
quit;

proc sql;
	create table mng1 as select a.*, b.post30v
	from mng1 as a left join temp as b
	on a.id=b.id;
quit;

data compq;		
	set m.fundq;
	if rdq ne .;
	keep gvkeyn fyearq fqtr datadate RDQ;
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;

proc sql;
	create table mng2 as select distinct a.*, b.tday as rdqtday
	from mng1 as a left join compq1 as b
	on a.gvkeyn=b.gvkeyn and (a.fpe-2)<=b.datadate<=(a.fpe+2);
quit;

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

proc sql;
	create table temp as select distinct a.id, exp(sum(log(1+b.ret)))-exp(sum(log(1+b.vwretd))) as postrdqv, count(b.ret) as obs
	from mng2 as a, crspd1a as b
	where a.permno=b.permno and (a.tday+2)<=b.tday<=(a.rdqtday+1)
	group by id;
quit;

proc freq data=temp; table obs; run;

proc sql;
	create table mng2 as select a.*, b.postrdqv
	from mng2 as a left join temp as b
	on a.id=b.id;
quit;

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

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

/*ods html close; ods html;*/

proc means data=mng2; run;

proc export data=mng2
	outfile= "Path\Management Forecast News JAR1.dta" 
	replace;
run; 


/**********************************************************************************/
/*add news variables from ravenpack*/
/**********************************************************************************/

proc import datafile="Path\Management Forecast News JAR1.dta" 
	replace out=sample;
run; 

proc import datafile="Path\RavenPack1.dta" 
	replace out=rp;
run; 

proc freq data=rp; table ens; run;

libname m 'Path'; run;
data cnames;
	set m.msenames;
run;

proc sql;
	create table sample1 as select a.*, b.ncusip
	from sample as a left join cnames as b
	on a.permno=b.permno and b.namedt<=a.anndats<=b.nameendt;
quit;

data temp;
	set sample1;
	keep id permno anndats ncusip;
run;

data rp1;
	set rp;
	if isin ne "";
	cusip6 = substr(isin,3,6);
	*if substr(isin,1,2) in ("US", "CA","MX");
	keep rpna_date_utc group type category  ens isin cusip6 rp_story_id;
run;

proc sort data=rp1 out=temp nodupkey; by rpna_date_utc; run;

data tday; set m.tday; run;

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

proc sort data=temp; by rpna_date_utc diff; run;
proc sort data=temp nodupkey; by rpna_date_utc; run;

proc sql;
	create table rp2 as select a.*, b.tday
	from rp1 as a, temp as b
	where a.rpna_date_utc=b.rpna_date_utc;
quit;

proc sql;
	create table rp3 as select a.id, a.gvkeyn, a.ncusip, a.anndats, b.group, b.type, b.category
	from sample1 as a left join rp2 as b
	on substr(a.ncusip,1,6)=b.cusip6 and (a.tday-1)<=b.tday<=(a.tday+1);
quit;

proc freq data=rp3;
	table group;
quit;

data rp4;
	set rp3;
	if group in ("dividends") then diva=1; else diva=0;
	if group in ("insider-trading") then ita=1; else ita=0;
	if group in ("revenues") then reva=1; else reva=0;
	if group in ("analyst-ratings") then ara=1; else ara=0;
	if group in ("acquisitions-mergers") then acqa=1; else acqa=0;
	if group in ("labor-issues") then lia=1; else lia=0;
	if group in ("investor-relations") then ira=1; else ira=0;
	if group in ("equity-actions") then eaa=1; else eaa=0;
run;

proc sql;
	create table rp5 as select distinct id, max(diva) as diva, max(ita) as ita, max(ara) as ara,
	max(acqa) as acqa, max(reva) as reva, max(lia) as lia, max(ira) as ira, max(eaa) as eaa
	from rp4
	group by id;
quit;

proc sql;
	create table sample2 as select a.*, b.*
	from sample1 as a left join rp5 as b
	on a.id=b.id;
quit;

proc means data=sample2; run;

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

proc export data=sample2
	outfile= "Path\Management Forecast News JAR1.dta" 
	replace;
run; 



/**********************************************************************/
/*add vix and other measures of uncertainty*/
/**********************************************************************/

proc import datafile= "Path\Management Forecast News JAR1.dta" 
	replace out=sample;
run; 

data sample;
	set sample;
	nmo = (year(anndats)-2000)*12+month(anndats);
run;

/*economic policy uncertainty index*/

proc import datafile= "Path\Categorical_EPU_Data.xlsx" 
	replace out=epu dbms=xlsx;
run; 

data epu1;
	set epu;
	dates = date - 21916;
	format dates date9.;
	if dates ne .;
	nmo = (year(dates)-2000)*12+month(dates);
run;

proc sql;
	create table sample1 as select a.*, b._1__Economic_Policy_Uncertainty as epu
	from sample as a left join epu1 as b
	on a.nmo=b.nmo;
quit;

proc sql;
	create table sample1 as select a.*, b._1__Economic_Policy_Uncertainty as epu1
	from sample1 as a left join epu1 as b
	on a.nmo=b.nmo+1;
quit;

/*investor sentiment from Baker and Wurgler*/

proc import datafile="Path\Investor_Sentiment_Data_20190327_POST.xlsx"
	replace out=sent dbms=xlsx;
	getnames=yes;
	sheet='DATA';
quit;

data sent;
	set sent;
	y=substr(yearmo,7,4)*1;
	m=substr(yearmo,11,2)*1;
	nmo = (y-2000)*12+m;
	sentn=sent*1;
run;

proc sql;
	create table sample1 as select distinct a.*, b.sentn as sentn
	from sample1 as a left join sent as b
	on a.nmo=b.nmo;
quit;

proc sql;
	create table sample1 as select distinct a.*,b.sentn as sentn1
	from sample1 as a left join sent as b
	on a.nmo=b.nmo+1;
quit;

/*michigan consumer sentiment*/

proc import datafile="Path\UMCSENT.xlsx"
	replace out=sent dbms=xlsx;
	getnames=yes;
quit;

data sent; set sent; nmo = (year(observation_date)-2000)*12+month(observation_date); run;

proc sql;
	create table sample1 as select distinct a.*, b.umcsent as umcsent
	from sample1 as a left join sent as b
	on a.nmo=b.nmo;
quit;

proc sql;
	create table sample1 as select distinct a.*, b.umcsent as umcsent1
	from sample1 as a left join sent as b
	on a.nmo=b.nmo+1;
quit;

/*vix*/

libname m 'Path'; run;
data vix; set m.cboe; run;
data tday; set m.tday; run;

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

proc sql;
	create table sample1 as select distinct a.*, b.vix as vixd1
	from sample1 as a left join vix1 as b
	on a.tday=b.tday+1;
quit;

proc sql;
	create table sample1 as select distinct a.*, b.vix as vixd2
	from sample1 as a left join vix1 as b
	on a.tday=b.tday+2;
quit;

proc sql;
	create table sample1 as select distinct a.*, b.vix as vixd3
	from sample1 as a left join vix1 as b
	on a.tday=b.tday+3;
quit;

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

proc means data=sample1; run;

proc export data=sample1
	outfile= "Path\Management Forecast News JAR1.dta" 
	replace;
run; 


/**********************************************************************/
/*VIX Recession corr*/
/**********************************************************************/

proc import datafile= "Path\Management Forecast News JAR1.dta" 
	replace out=mng;
run; 

data vix; set m.cboe; run;

data tday; set m.tday; run;

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

data vix2;
	set vix1;
	nmo=(year(date)-2000)*12+month(date);
	if 2002<=year(date)<=2017;
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 vix3 as select a.*, b.recession
	from vix2 as a left join rec as b
	on b.bnmo<=a.nmo<=b.enmo;
quit;

proc sql;
	create table vix3 as select distinct nmo, recession, avg(vix) as vix, count(vix) as obs
	from vix3
	group by nmo;
quit;

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

proc ttest data=vix3;
	class recession;
	var vix;
run;

