options errorabend;
libname ibes '../data/raw';
libname crsp '../data/raw';
libname perm "../data";

data guide; set ibes.det_guidance 
(keep = ticker pdicity measure curr anndats prd_yr prd_mon eefymo val_1 val_2 mean_at_date usfirm);
where (measure = "EPS") and (curr = "USD") and (usfirm = 1) and (year(anndats) lt 2021) /*and (ticker in ('MSFT','HWP','SNO','AAPL'))*/ ;
guide_period = intnx('month',mdy(prd_mon,1,prd_yr),0,'e');
year = year(anndats);
if val_2 eq . and val_1 ne . then do;
	range = 0;
	mgmt_fcst = val_1;
end;
if val_2 ne . and val_1 ne . then do;
	range = 1;
	mgmt_fcst = mean(val_1,val_2);
end;
drop curr measure usfirm;
format guide_period YYMMDDN8.;
if mean_at_date ne .; /* Maybe fill in these values using ibes.statsum at some later date*/
run;

proc sort data = guide nodupkey; by ticker pdicity guide_period anndats ;
run;

/* Get Actual EPS, EA and lag EA Dates*/
proc sort data = ibes.statsum_epsus out = EAs (keep = ticker fpedats fpi fiscalp actual anndats_act) nodupkey; by ticker fiscalp fpedats ;
where (usfirm = 1) and (2002 le year(fpedats) lt 2021) /*and (ticker in ('MSFT','HWP','SNO','AAPL')) and (fpi in ("1","6"))*/ and (fiscalp ne "LTG");
run;

proc sort data=EAs out=EAtemp (keep = ticker fpi fpedats anndats_act) nodupkey; by ticker fpi fpedats;
run;

proc printto log="../logs/proc_expand_junk1.log" ; run;
proc expand data=EAtemp out=EAtemp1 method=none;	
convert anndats_act = anndats_act_tm1 / transform=(lag 1);	
by ticker fpi;
run;	
proc printto; run;

proc sql; create table EAs1 as select
	a.*, b.anndats_act_tm1
	from EAs a left join EAtemp1 b
	on a.ticker eq b.ticker
	and a.fpedats eq b.fpedats
	and a.fpi = b.fpi;
	quit;

proc sql;
	create table guide2 as select
	a.*, b.actual, b.anndats_act, b.anndats_act_tm1
	from guide as a left join EAS1 as b
	on a.ticker = b.ticker
	and a.guide_period = b.fpedats
	and a.pdicity = b.fiscalp;
quit;

 *Get analyst Data so I can compare analyst accuracy to manager accuracy;
data x; set ibes.statsum_epsus
(keep = ticker cusip anndats_act statpers measure fpi fiscalp meanest usfirm fpedats actual);						
where /*ticker in ('MSFT', 'HWP', 'SNO', 'AAPL') and*/ (year(fpedats) ge 2002) and (year(fpedats) le 2021) and
		(fiscalp ne "LTG") and (fpi in ("1","6")) and (measure = "EPS") and (usfirm = 1);
drop usfirm measure;
run;

proc sort data=x out=xtemp (keep = ticker fpi fpedats anndats_act) nodupkey; by ticker fiscalp fpedats;
run;
proc printto log="../logs/proc_expand_junk1.log" ; run;
proc expand data=xtemp out=xtemp1 method=none;	
convert anndats_act = anndats_act_tm1 / transform=(lag 1);	
by ticker fpi;
run;	
proc printto; run;

proc sql; create table x1 as select
	a.*, b.anndats_act_tm1
	from x a left join xtemp1 b
	on a.ticker eq b.ticker
	and a.fpedats eq b.fpedats
	and a.fpi = b.fpi;
	quit;

data x2; set x1;
where (statpers gt anndats_act_tm1) and (statpers le anndats_act_tm1 + 45);
run;

proc sort data = x2; by ticker fpi fpedats statpers ;
run;

data x3; set x2; by ticker fpi fpedats;
if first.fpedats;
run;

* get beginning of year price;
proc sql;
	create table x4 as select
	a.*, b.permno
	from x3 a left join ibes.ibcrsphist b
	on a.ticker = b.ticker;
quit;

proc sql;
	create table x5 as select
	a.*, abs(b.prc) as prc_beg_yr, b.date
	from x4 as a left join crsp.dsf b
	on a.permno = b.permno
	and a.fpedats-365 lt b.date lt a.fpedats-358
	having abs(b.prc) ge 1;
quit;

proc sort data = x5 nodupkey; by ticker fiscalp fpedats statpers date; /* There may be multiple permnos for a few tickers that cause duplicate observations by this sort*/
run;

data x6; set x5; by ticker fiscalp fpedats statpers date;
if first.statpers;
drop date;
run;

proc sql;
	create table x7 as select
	a.*, b.anndats, b.actual as actual_gd, b.mgmt_fcst
	from x6 as a left join guide2 as b
	on a.ticker = b.ticker
	and a.fpedats = b.guide_period
	and a.fiscalp = b.pdicity;
quit;

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

* Restrict to first guidance;
data x8; set x7; by ticker fiscalp fpedats;
if first.fpedats;
run;

data x9; set x8;
mgmt_error = abs(mgmt_fcst - actual);
analys_error = abs(meanest - actual);
mgmt_horizon = anndats - anndats_act;
analys_horizon = statpers - anndats_act;
dif_error = analys_error - mgmt_error;
if dif_error ne . then mgr = analys_error > mgmt_error; /*following Hutton, Lee, and Shu (2012)*/
mgr_sub = mgr;
if dif_error = . then mgr_sub = 0;
run;

proc means data = x9 n mean std min p25 p50 p75 max;
var mgr mgr_sub dif_error;
run;

proc means data = x9 n mean std min p25 p50 p75 max;
where mgr ne .;
var mgr dif_error mgmt_horizon analys_horizon;
class fiscalp;
run;

data perm.fcst_errors; set x9;
run;
