/*****************************************************************************************/
/*This code replicates the code in "Management Forecasts in Bad Times 3-31-2021.sas" for 
our mai analyses but also adds individual analyst forecasts to the management forecast
sample*/
/*****************************************************************************************/

/*get IBES data on management and analyst forecasts*/

libname m 'Path'; run;

data ibes5; set m.ibes5; run;

proc sql;
	create table temp as select a.eid, a.anndats, b.value, b.cfacshr, b.eid, b.estimator, b.analys, 
	b.anndats as ianndats, b.astpdats, b.excdats, b.anntims as ianntims
	from ibes5 as a inner join ibes5 as b
	on a.ticker=b.ticker and a.pdicity=b.pdicity and a.fpedats=b.fpedats 
	and (a.anndats-30)<=b.anndats<a.anndats
	order by a.eid, 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 eid estimator ianndats ianntims;
run;

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

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

proc sql;
	create table ibes6 as select a.*, b.meanest as meanest30, b.numest as numest30,
	b.stddev as stddev30, b.medest as medest30
	from ibes5 as a left join tempm1 as b
	on a.eid=b.eid;
quit;

data ibes6;
	set ibes6;
	anndatso=anndats;
	format anndatso date9.;
	if anntims>'16:00:00't then anndats=anndats+1;
	format anndats date9.;
run;

proc means data=ibes6 n mean std p5 p25 p50 p75 p95; run;

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

data ibes7;
	set ibes6;
	mng=0;
	if 2002<=year(anndats)<=2017;
	if pdicity="A" then qtrfor=0;
	if pdicity="Q" then qtrfor=1;
	keep permno ticker mng analys fpedats anndats eid mng estimator pdicity qtrfor
	meanest30 numest30 medest30 stddev30;
run;

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

data mng1;
	set mng;
	eid = id + 10000000000;
	mng=1;
	fpedats=fpe;
	if pdicity="ANN" then qtrfor=0;
	if pdicity="QTR" then qtrfor=1;
	keep permno ticker fpedats anndats mng eid news3 pdicity meanest30 numest30 medest30 stddev30 qtrfor;
run;

data mng2;
	set mng1 ibes7;
	analysid=analys*1;
	if analysid=. then analysid=permno+100000000;
	if qtrfor in (0,1);
	nmo=(year(anndats)-2000)*12+month(anndats);
	if month(anndats) in (1,2,3) then qtr=1;
	if month(anndats) in (4,5,6) then qtr=2;
	if month(anndats) in (7,8,9) then qtr=3;
	if month(anndats) in (10,11,12) then qtr=4;
	qobs = (year(anndats)-2000)*4+qtr;
run;

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

proc sort data=mng2; by permno fpedats qtrfor nmo; run;


/*calculate CARs/BHARs using Eventus*/

data events;
	retain permno anndats;
	set mng2;
	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), (0,0), (0,1);
evtstudy pre=1 post=1 both 
outwin=work.carmm nonames noprint;
 
proc download data=carmm; run;                                                     
endrsubmit;    

proc sql;
	create table mng3 as select a.*, b.car_window_1 as carmmv3,  b.car_window_2 as carmmv1,  b.car_window_3 as carmmv2
	from mng2 as a left join carmm as b
	on a.permno=b.permno and a.anndats=b.original_eventdat and (b._weight_="Value");
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 mng3 as select a.*, b.gvkeyn
	from mng3 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;

data tday; set m.tday; run;

proc sort data=mng3 out=temp nodupkey; by anndats; run;

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

proc sort data=temp1; by anndats diff; run;
proc sort data=temp1 nodupkey; by anndats; run;

proc sql;
	create table mng5 as select distinct a.*, b.tday
	from mng3 as a, temp1 as b
	where a.anndats=b.anndats;
quit;

proc sort data=mng5 nodupkey; by eid; 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 distinct a.*, b.rdq, count(b.rdq) as bundled
	from mng5 as a left join compq1 as b
	on a.gvkeyn=b.gvkeyn and (a.tday-2)<=b.tday<=(a.tday+2)
	group by eid;
quit;

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



/*create compustat and other 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 atq ceqq prccq cshoq;
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 eid diffc; run;
proc sort data=mng7 nodupkey; by eid; run;


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;

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 eid diffi; run;
proc sort data=mng8 nodupkey; by eid; run;

proc export data=mng8
	outfile= "Path\Management vs Analyst Value Full.dta" 
	replace;
run; 



/*****************************************************************/
/*Create bundled forecast adjustment*/
/*****************************************************************/

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;

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;

data tday; set m.tday; 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 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 cfacshr prc cfacpr ret;
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;                    

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 m.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 m.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;

data compq8;
	set compq8;
 	drop pdicity;
run;

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;

/*ods html close; ods html;*/

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;

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 vs Analyst Value Full.dta" out=mng
	replace;
run;

data mng;
	set mng;
	pdicitys=pdicity;
	if pdicitys="ANN" then pdicitys="A";
	if pdicitys="QTR" then pdicitys="Q";
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.fpedats=b.fpedats
	and a.pdicitys=b.pdicity;
quit;

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

/*calculate downturn measure and other 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;

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

proc sql;
	create table mng3a as select a.*, b.recession
	from mng3a 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 mng3a;
	set mng3a;
	y=year(anndats); m=month(anndats);
	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;
run;

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

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 mng3a 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 eid ndiff; run;
proc sort data=mng4a nodupkey; by eid; run;

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 sort data=mng4a out=temp nodupkey; by permno nmod; run;

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

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

data tday; set m.tday; run;

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

data index;	set m.dsi; run;

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

proc sql;
	create table crsp1 as select a.*, b.tday
	from crsp1 as a inner join tday as b
	on a.date=b.date;
quit;

proc sort data=mng4a out=temp nodupkey; by permno tday; run;

proc sql;
	create table temp as select distinct a.permno, a.tday, 
	exp(sum(log(1+b.ret)))-exp(sum(log(1+b.vwretd))) as bhar20, count(b.ret) as obs1
	from temp as a, crsp1 as b
	where a.permno=b.permno and (a.tday-21)<=b.tday<=(a.tday-2)
	group by a.permno, a.tday;
quit;

proc sql;
	create table mng4a as select a.*, b.bhar20
	from mng4a as a left join temp as b
	on a.permno=b.permno and a.tday=b.tday;
quit;

proc sql;
	create table mng4a as select a.*, b.shrout*b.cfacshr as shareadj
	from mng4a as a left join crsp1 as b
	on a.permno=b.permno and a.tday=b.tday;
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 crsp1 as b
	on a.permno=b.permno and (a.datadate)<=b.date<=(a.datadate+4);
quit;

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

proc sql;
	create table mng4b as select a.*, b.value, b.cfacshr
	from mng4b as a left join m.ibes5 as b
	on a.eid=b.eid;
quit;

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

data mng;
	set mng;
	eid = id + 10000000000;
run;

proc sql;
	create table mng4b as select a.*, b.mngfor as valuea, b.score
	from mng4b as a left join mng as b
	on a.eid=b.eid;
quit;

proc sort data=mng4b nodupkey out=mng4c; by eid; run;

proc means data=mng4c; run;

proc export data=mng4c
	outfile= "Path\Management vs Analyst Value Full1.dta" 
	replace;
run; 

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

data mng5;
	set mng4c;
	if bundled>1 then bundled=1;
	if mng=0 then value=value/cfacshr;
	if value=. then value=valuea;
	if fpedats ne . and anndats ne .;
	if recession=. then recession=0;
	lnmve = log(mve/cpi);
	if pgdp ne . then dtgdp=0; if pgdp ne . and pgdp<0 then dtgdp=1;
	lnanal = log(numest30);
	horizon = (fpedats-anndats)/365;
	if horizon>=0;
	year = year(anndats);
	if io=. then io=0;
	if io>1 then io=1;
	if 2002<=year<=2017;
	if pdicitys in ("A","Q");

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

	earnings=medest30*shareadj;
	valueadj = value*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 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);
run; 

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

proc sort data=mng5; by permno fpedats mng anndats; run;

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

proc sql;
	create table mng5 as select *, mean(mng) as mean
	from mng5
	group by permno, fpedats, qtrfor;
quit;

proc sql;
	create table mng5 as select *, mean(mng) as meanqtr
	from mng5
	group by permno, qobs;
quit;

proc sql;
	create table mng5 as select *, mean(mng) as meanp
	from mng5
	group by permno;
quit;

data mng5a;
	set mng5;
	if 0<mean<1 then keep1=1;
	if 0<meanqtr<1 then keep2=1;
	if 0<meanp<1 then keep3=1;
run;

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

proc sql;
	create table mng6a as select *, mean(news1) as news1a, mean(news4) as news4a
	from mng6
	group by permno, anndats, fpedats, pdicity, mng;
quit;

proc sort data=mng6a out=mng6a nodupkey; by permno fpedats pdicity mng anndats; run;

options msglevel=i; %include 'Path\macros.sas';
%WT(data = mng6a, out=mng6a, byvar=mng, 
	vars =  carmmv1 news1a news4a  
	blev cfa oroa lnmve mb mbe lnanal esurp esurp1 rvol1  horizon bhar20  nsameday1 ,
		type = W, pctl = 1 99, drop=N);
run;

proc means data=mng6a n mean std min p1 p5 p50 p95 p99 max;
*	where mng=1;
	where keep1=1;
	var carmmv1 news1a news4a analysid;
run;

proc export data=mng6a
	outfile= "Path\Management vs Analyst Value Full2.dta" 
	replace;
run; 
