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


data guide; set ibes.det_guidance 
(keep = ticker pdicity measure curr range_desc 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_guide = year(anndats);
if val_2 eq . and val_1 ne . then do;
	range = 0;
	mgmt_fcst = val_1;
	precision = 2;
end;
if val_2 ne . and val_1 ne . then do;
	range = 1;
	mgmt_fcst = mean(val_1,val_2);
	precision = 1;
end;
drop curr measure usfirm;
format guide_period YYMMDDN8.;
run;

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


/* Get 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_junk.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.range_desc, a.anndats, a.prd_yr,a.prd_mon,a.eefymo,a.val_1,a.val_2,a.mean_at_date,a.guide_period,a.year_guide,a.range,a.mgmt_fcst,a.precision, 
	coalesce(a.ticker,b.ticker) as ticker, coalesce(a.guide_period,b.fpedats) as fpedats format=date9., coalesce(a.pdicity,b.fiscalp) as fiscalp, b.actual, b.anndats_act, b.anndats_act_tm1
	from guide as a right join EAS1 as b
	on a.ticker = b.ticker
	and a.guide_period = b.fpedats
	and a.pdicity = b.fiscalp;
quit;

data guide2; set guide2;
if mgmt_fcst ne . then guide = 1; else guide = 0;
if mgmt_fcst = . then precision = 0;
if fiscalp = "ANN" and guide = 1 then annual_guidance = 1; else annual_guidance = 0;
if fiscalp = "QTR" and guide = 1 then qtrly_guidance = 1; else qtrly_guidance = 0;
if anndats_act = . then delete;
*	annual_guidance = .; 
*	qtrly_guidance = .;
*	guide = .;
*end;
year = year(fpedats);
run;



proc sql;
	create table guide3 as select distinct
	*, max(guide) as guide2
	from guide2 group by ticker, fpedats;
quit;

proc sql;
	create table guide4 as select distinct
	*, max(annual_guidance) as annual_guidance2, max(qtrly_guidance) as qtrly_guidance2
	from guide3 group by ticker, year;
quit;

data guide4; set guide4;
drop guide annual_guidance qtrly_guidance;
rename guide2 = guide annual_guidance2 = annual_guidance qtrly_guidance2 = qtrly_guidance;
run;

proc sort data = guide4; by ticker fpedats descending fiscalp ;
run;


proc sort data = guide4 out = guide_qtr nodupkey; by ticker fpedats anndats fiscalp;
where fiscalp = "QTR";
run;

proc sql;
	create table guide_qtr2 as select distinct
	ticker, fpedats, count(mgmt_fcst) as guide_freq
	from guide_qtr group by ticker, fpedats;
quit;

proc sort data = guide4 out = guide_ann nodupkey; by ticker fpedats anndats fiscalp;
where fiscalp = "ANN";
run;

proc sql;
	create table guide_ann2 as select distinct
	ticker, fpedats, count(mgmt_fcst) as guide_freq
	from guide_ann group by ticker, fpedats;
quit;

proc sql;
	create table guide5 as select distinct
	a.*, b.guide_freq as qtr_guide_freq, c.guide_freq as ann_guide_freq
	from guide4 as a
	left join guide_qtr2 as b on a.ticker = b.ticker and a.fpedats = b.fpedats
	left join guide_ann2 as c on a.ticker = c.ticker and a.fpedats = c.fpedats
	order by ticker, fpedats, fiscalp;
quit;


proc sort data = guide5 nodupkey; by ticker fpedats;
run;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=guide5 out=guide_rolling method=none;
   convert guide=guide_roll_sum / transformout=(movsum 12 trimleft 2);
   convert annual_guidance=annual_guidance_roll_sum / transformout=(movsum 12 trimleft 2);
   convert qtrly_guidance=qtrly_guidance_roll_sum / transformout=(movsum 12 trimleft 2);
   convert qtr_guide_freq=qtr_guide_freq_roll / transformout=(movave 12 trimleft 2);
   convert ann_guide_freq=ann_guide_freq_roll / transformout=(movave 12 trimleft 2);
   convert precision=precision_roll / transformout=(movave 12 trimleft 2);
	by ticker;
run;
proc printto; run;

*later classify as annual or quarterly guider or both;
data guide_rolling2; set guide_rolling;
if guide_roll_sum > 0 then guider = 1; else guider = 0;
if annual_guidance_roll_sum > 0 then guider_ann = 1; else guider_ann = 0;
if qtrly_guidance_roll_sum > 0 then guider_qtr = 1; else guider_qtr = 0;
if guide_roll_sum = . then do;
	guider = .;
	guider_ann = .;
	guider_qtr = .;
end;
keep ticker fpedats guide guider guider_ann guider_qtr qtr_guide_freq ann_guide_freq qtr_guide_freq_roll ann_guide_freq_roll precision precision_roll;
label guide="Provides Guidance in Current Quarter" guider="Provides Guidance in the Past 3 Years";
run;

data perm.mf_disclosure; set guide_rolling2;
run;


proc means data = perm.mf_disclosure n mean std min p1 p25 p50 p75 p99 max;
var guide guider guider_ann guider_qtr qtr_guide_freq ann_guide_freq qtr_guide_freq_roll ann_guide_freq_roll precision precision_roll;
run;

