options errorabend;

libname perm "../data";
libname ibes "../data/raw";
libname tone '../../../../../Data/ThomsonOne';


data recddet; set perm.rec_obs ;
where /*ticker in ("AA3E","AAPL","AIR","ALCO","ALEX","HWP","MSFT","SNO") and*/ (rec5_lvl ne .);
if rec5_lvl < 4 then nbuy = 1;
else nbuy = 0;
upgrade = upgrade_5;
if upgrade_5 = . then upgrade = 0;
downgrade = downgrade_5;
if downgrade_5 = . then downgrade = 0;
run;

proc sort data = recddet; by ticker estimid amaskcd anndats anntims;
run;

* keep the last recommendation observation on a day;
data recddet_1; set recddet; by ticker estimid amaskcd anndats;
if last.anndats;
run;

* Get # days each since the prior recommendation change - excluding same day revisions;
data recddet_2; set recddet_1; by ticker estimid amaskcd ;
rec_lvl_tm1 = lag(rec5_lvl);
retain rec_since_date;
if first.amaskcd then rec_lvl_tm1 = .;
rec_chg = rec5_lvl - rec_lvl_tm1;
if first.amaskcd then rec_since_date = anndats;
else if abs(rec_chg) > 0 then rec_since_date = anndats;
rec_since_date2 = lag(rec_since_date);
if first.amaskcd then rec_since_date2 = .;
days_valid_rec = anndats - rec_since_date2;
format rec_since_date rec_since_date2 date9.;
drop rec_since_date rec_since_date2 rec_lvl_tm1 rec_chg;
run;

proc sql;
	create table recddet_3 as select distinct
	a.*, b.permno
	from recddet_2 as a left join ibes.ibcrsphist as b
	on a.ticker = b.ticker
	and (b.sdate-90 le a.anndats or b.sdate = .B)
	and (a.anndats le b.edate+90 or b.edate = .E);
quit;

proc sort data = recddet_3 nodupkey; by ticker amaskcd datetime_1;
run;

data x10; set perm.ea_analyst;
run;
* Merge in recommendation data in 3 weeks following the call;
proc sql;
	create table x10a as select distinct
	a.*, b.anndats as rec_anndats_postea, b.rec5_chg as rec5_chg_postea, 
		b.rec5_lvl as rec5_lvl_postea, b.upgrade as upgrade_postea, b.downgrade as downgrade_postea, b.days_valid_rec
	from x10 as a left join recddet_3 as b
	on a.permno = b.permno
	and a.amaskcd = b.amaskcd
	/*looking to see if analyst changes recommendation in the 21 days following the EA*/
	and a.mostimportantdateutc le b.anndats <= a.mostimportantdateutc + 21; 
quit;

proc sort data = x10a out = x11 nodupkey; by ticker amaskcd mostimportantdateutc rec_anndats_postea;
run;

* keep first recommendation after EA to get immediate reaction;
data x12; set x11; by ticker amaskcd mostimportantdateutc ;
if first.mostimportantdateutc;
array vars rec5_chg_postea upgrade_postea downgrade_postea;
do over vars;
	if vars = . then vars = 0;
end;
run;

proc sql;
	create table x13 as select distinct
	a.*, mean(nonanswer) as nonanswer, count(nonanswer) as n_questions_
	from x12 as a left join perm.cc_glz_analysts as b
	on a.permno = b.permno
	and a.mostimportantdateutc = b.mostimportantdateutc
	and a.amaskcd = b.amaskcd
	group by a.permno, a.mostimportantdateutc, a.amaskcd;
quit;

proc sort data = x13 nodupkey; by ticker amaskcd mostimportantdateutc ; run;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data = x13 out = x14 method = none;
	convert bearish=bearish_tm1 / transformout=(lag 1) ;
	convert sell=sell_tm1 / transformout=(lag 1) ;	
	convert downgrade=downgrade_tm1 / transformout=(lag 1) ;	
	by ticker amaskcd;
run;
proc printto; run;

data x15; set x14; by ticker amaskcd ;
if bearish ne bearish_tm1 then bearish_chg = 1;
else bearish_chg = 0;
retain date_first;
if first.amaskcd then date_first = mostimportantdateutc;
else if bearish ne bearish_tm1 then date_first = mostimportantdateutc;
bearish_length = (mostimportantdateutc - date_first)/365;
drop date_first;
run;

* Add EPS revisions after Earnings Call;
proc sql;
	create table x15a as select distinct
	a.*, b.anndats as eps_anndats, b.pctchg_eps, b.chg_eps_unscaled, abs_pctchg_eps
	from x15 as a left join perm.eps_annual as b
	on a.ticker = b.ticker
	and a.amaskcd = b.analys
	and a.mostimportantdateutc le b.anndats le a.mostimportantdateutc + 7
	;
quit;

proc sort data = x15a nodupkey; by ticker amaskcd mostimportantdateutc eps_anndats;
run;

data x15aa; set x15a; by ticker amaskcd mostimportantdateutc;
if first.mostimportantdateutc;
if pctchg_eps > 0 then eps_up = 1;
else if pctchg_eps ne . then eps_up = 0;
if pctchg_eps < 0 and pctchg_eps ne . then eps_down = 1;
else if pctchg_eps ne . then eps_down = 0;
eps_timeliness = eps_anndats - mostimportantdateutc;
drop eps_anndats;
run;

* Add TP revisions after Earnings Call;
proc sql;
	create table x15ab as select distinct
	a.*, b.anndats as tp_anndats, b.pctchg_tp, b.abs_pctchg_tp
	from x15aa as a left join perm.tp_revisions as b
	on a.ticker = b.ticker
	and a.amaskcd = b.amaskcd
	and a.mostimportantdateutc le b.anndats le a.mostimportantdateutc + 7
	;
quit;

proc sort data = x15ab nodupkey; by ticker amaskcd mostimportantdateutc tp_anndats;
run;

data x15ac; set x15ab; by ticker amaskcd mostimportantdateutc;
if first.mostimportantdateutc;
if pctchg_tp > 0 then tp_up = 1;
else if pctchg_tp ne . then tp_up = 0;
if pctchg_tp < 0 and pctchg_tp ne . then tp_down = 1;
else if pctchg_tp ne . then tp_down = 0;
tp_timeliness = tp_anndats - mostimportantdateutc;
drop tp_anndats;
run;

* Get analyst tone pre and post call and calculate change;
proc sql;
	create table x15ad as select distinct
	a.*, b.tone_finbert_daily as pre_tone, b.pubdate as tone_date
	from x15ac as a left join tone.analystreport_merged as b
	on a.ticker = b.ibes_ticker
	and a.amaskcd = b.amaskcd
	and a.mostimportantdateutc-360 le b.pubdate lt a.mostimportantdateutc-1
	;
quit;

proc sort data = x15ad nodupkey; by ticker amaskcd mostimportantdateutc tone_date;
run;

data x15ae; set x15ad; by ticker amaskcd mostimportantdateutc;
if last.mostimportantdateutc;
tone_days = mostimportantdateutc - tone_date;
drop tone_date;
run;

* Get analyst tone post call and calculate change;
proc sql;
	create table x15af as select distinct
	a.*, b.tone_finbert_daily as post_tone, b.pubdate as tone_date
	from x15ae as a left join tone.analystreport_merged as b
	on a.ticker = b.ibes_ticker
	and a.amaskcd = b.amaskcd
	and a.mostimportantdateutc le b.pubdate lt a.mostimportantdateutc+7
	;
quit;

proc sort data = x15af nodupkey; by ticker amaskcd mostimportantdateutc tone_date;
run;

data x15ag; set x15af; by ticker amaskcd mostimportantdateutc;
if first.mostimportantdateutc;
tonechg = post_tone - pre_tone;
if tonechg > 0 then tone_up = 1;
else if tonechg ne . then tone_up = 0;
if tonechg < 0 and tonechg ne . then tone_down = 1;
else if tonechg ne . then tone_down = 0;
tone_timeliness = tone_date - mostimportantdateutc;
drop tone_date;
run;


proc sql;
	create table x15b as select distinct
	a.*, b.datadate, b.cik, b.siccd, b.lnmve, b.btm, b.lnfollow, b.lev, b.bhar_yr, b.stdret_yr
	from x15ag as a left join perm.wrds_data as b
	on a.ticker = b.ibesticker
	and b.rdq-15 le a.mostimportantdateutc le b.rdq + 15;
quit;

proc sort data = x15b nodupkey; by ticker amaskcd mostimportantdateutc datadate;
run;

data x16; set x15b; by ticker amaskcd mostimportantdateutc;
if last.mostimportantdateutc;
sic2 = substr(left(siccd),1,2);
run;

proc means data = x16; 
var pctchg_eps pctchg_tp;
run;

/* Merge in analyst controls */
data ac; set perm.analyst_controls;
run;

data ee_merge; set perm.estimid_estimator_merge;
run;

proc sql;
	create table x17 as select distinct
	a.*, b.*
	from x16 as a left join ee_merge as b
	on a.estimid = b.estimid;
quit;

proc sort data = x17 (drop=n_estimid percent_estimator) out = x18 nodupkey; by ticker amaskcd mostimportantdateutc descending n_estimid_estimator;
run;

proc sql;
	create table x18 as select distinct
	a.*, b.*
	from x17 as a left join ac as b
	on a.amaskcd = b.analys
	and a.estimator = b.estimator
	and a.ticker = b.ticker
	and year(a.mostimportantdateutc) = b.annyear;
quit;

%let outfile = ea_analyst_2;
data perm.&outfile ; set x18;
run;