options errorabend;

libname perm "../data" ;
libname crsp "../data/raw" ;

data wrds_data; set perm.wrds_data;
sic2 = substr(left(siccd),1,2);
run;


data bearish; set perm.firm_qtr_bearish;
WHERE (t_year < 2020) and (t_year > 2005) and (n_analysts > 1) and (permno ne .);
run;


data bearish1; set bearish;
where (n_analysts_on_call ne .);
run;

data bearish2; set bearish1;
where (firm_qtr_bearish_ind_rmean ne .) and (bearish_in_roll ne .) and (max_bearish_priority_roll ne .);
run;

proc sql;
	create table x as select distinct
	a.*, b.mostimportantdateutc, b.firm_qtr_bearish_ind, b.firm_qtr_bearish_ind_rmean, b.bearish_in_roll, 
		b.bearish_in_firm_qtr, b.avg_n_answers, 
		b.avg_n_questions, b.avg_questions_bearish, b.avg_answers_bearish, b.prin_sir,
		b.max_bearish_priority_roll, b.max_bearish_priority, b.n_analysts, b.n_analysts_on_call, (b.n_analysts-b.n_analysts_on_call) as n_analysts_off_call,
		b.n_bearish, b.n_bearish_in, b.n_bearish_out, b.pct_bearish, b.n_in, b.n_out
	from wrds_data as a inner join bearish as b
	on a.permno = b.permno
	and a.rdq-15 le b.mostimportantdateutc le a.rdq+15
	order by gvkey, datadate, mostimportantdateutc;
quit;

proc sort data = x nodupkey; by gvkey datadate;
run;

* Summarize interactions with different types of analysts;
data cc_indiv; set perm.cc_glz_analysts;
run;

proc sql;
	create table cc_indiv as select distinct
	*, max(p_order) as max_order
	from cc_indiv group by permno, mostimportantdateutc;
quit;

data cc_indiv; set cc_indiv;
priority = 1 - ((p_order-1) / max_order);
run;

proc sql;
	create table cc as select distinct
	permno, mostimportantdateutc, mean(nonanswer) as nonanswer, count(nonanswer) as n_questions
	from cc_indiv group by permno, mostimportantdateutc;
quit;

proc sql;
	create table cc2 as select distinct
	permno, mostimportantdateutc, mean(nonanswer) as nb_nonanswer, count(nonanswer) as n_nb_questions
	from cc_indiv where bearish = 1 
	group by permno, mostimportantdateutc
	;
quit;

proc sql;
	create table cc2nb as select distinct
	permno, mostimportantdateutc, mean(nonanswer) as bearish_nonanswer, count(nonanswer) as n_bearish_questions
	from cc_indiv where bearish = 0
	group by permno, mostimportantdateutc
	;
quit;

proc sql;
	create table cc3 as select distinct 
	permno, mostimportantdateutc, amaskcd, bearish, n_questions, n_answers, p_order, priority
	from cc_indiv;
quit;

proc sql;
	create table cc3 as select distinct
	permno, mostimportantdateutc, amaskcd, bearish, n_questions, n_answers, count(n_questions) - 1 as n_followups, min(p_order) as p_order, min(priority) as priority
	from cc3 group by permno, mostimportantdateutc, amaskcd;
quit;

proc sort data = cc3 nodupkey; by permno mostimportantdateutc amaskcd;
run;

proc sql;
	create table cc3a as select distinct
	permno, mostimportantdateutc, mean(n_answers) as mean_answers, mean(n_questions) as mean_questions, mean(p_order) as mean_order, mean(priority) as mean_priority, mean(n_followups) as mean_followups
	from cc3 group by permno, mostimportantdateutc;
quit;

proc sql;
	create table cc3nb as select distinct
	permno, mostimportantdateutc, mean(n_answers) as mean_nb_answers, mean(n_questions) as mean_nb_questions, mean(p_order) as mean_nb_order, mean(priority) as mean_nb_priority, mean(n_followups) as mean_nb_followups
	from cc3 where bearish = 0
	group by permno, mostimportantdateutc
	;
quit;

proc sql;
	create table cc3b as select distinct
	permno, mostimportantdateutc, mean(n_answers) as mean_bearish_answers, mean(n_questions) as mean_bearish_questions, mean(p_order) as mean_bearish_order, mean(priority) as mean_bearish_priority, mean(n_followups) as mean_bearish_followups
	from cc3 where bearish = 1
	group by permno, mostimportantdateutc
	;
quit;

proc sort data=x nodupkey; by permno mostimportantdateutc; run;
proc sort data=cc nodupkey; by permno mostimportantdateutc; run;
proc sort data=cc2 nodupkey; by permno mostimportantdateutc; run;
proc sort data=cc2nb nodupkey; by permno mostimportantdateutc; run;
proc sort data=cc3a nodupkey; by permno mostimportantdateutc; run;
proc sort data=cc3nb nodupkey; by permno mostimportantdateutc; run;
proc sort data=cc3b nodupkey; by permno mostimportantdateutc; run;

data x2;
	merge x (in=a) cc cc2nb cc2 cc3a cc3nb cc3b;
	by permno mostimportantdateutc;
if a;
run;

* Merge in management guidance behavior ;
data mf_disc; set perm.mf_disclosure;
run;

data x3; set x2;
if guider = . then guider = 0;
if n_guidance = . then n_guidance = 0;
run;

proc sql;
	create table x4 as select distinct
	a.*, b.qtr_guide_freq_roll, b.ann_guide_freq_roll, b.precision_roll, b.precision, b.guide, b.qtr_guide_freq, b.ann_guide_freq, b.guider as guider2, b.guider_ann, b.guider_qtr
	from x3 as a left join mf_disc as b
	on a.ibesticker = b.ticker
	and a.datadate = b.fpedats;
quit;

data x4; set x4;
array vars qtr_guide_freq_roll ann_guide_freq_roll precision_roll precision guide qtr_guide_freq ann_guide_freq guider2 guider_ann guider_qtr;
do over vars;
	if missing(vars) then vars = 0;
end;
array vars2 qtr_guide_freq_roll ann_guide_freq_roll ann_guide_freq qtr_guide_freq n_guidance;
array lnvars ln_qtr_guide_freq_roll ln_ann_guide_freq_roll ln_ann_guide_freq ln_qtr_guide_freq ln_n_guidance;
do over vars2;
	lnvars = log(1+vars2);
end;
mf_disc_roll_qtr = ln_qtr_guide_freq_roll*guider*precision_roll;
mf_disc_qtr = ln_qtr_guide_freq*guide*precision;
mf_disc_roll_ann = ln_ann_guide_freq_roll*guider*precision_roll;
mf_disc_ann = ln_ann_guide_freq*guide*precision;
run;

proc sort data = x4 nodupkey; by gvkey datadate;
run;

* Bring in prior management accuracy;
data accuracy; set perm.Mf_mkt_resp;
run;

proc sql;
	create table x5 as select distinct
	a.*, b.mgmt_anndats, b.mgmt_accuracy_adj, b.rel_accurate, b.prior_rel_acc_3yrs, b.prior_mgmt_acc_adj_3yrs
	from x4 as a left join accuracy as b
	on a.ibesticker = b.ticker
	and a.datadate = b.guide_period;
quit;

proc sort data = x5 nodupkey; by gvkey datadate descending mgmt_anndats;
run;

proc sort data = x5 nodupkey; by gvkey datadate;
run;


PROC PRINCOMP DATA= x5 OUT=outdata OUTSTAT = stats;
VAR bearish_in_firm_qtr firm_qtr_bearish_ind max_bearish_priority;
RUN;

PROC CORR DATA=outdata;
VAR bearish_in_firm_qtr firm_qtr_bearish_ind max_bearish_priority;
WITH prin1 prin2 prin3;
run;
/* keep only prin1 as it is most related to all three variables*/
/* Same result as stata command: factor bearish_in_roll firm_qtr_bearish_ind_rmean max_bearish_priority_roll, pfc; rotate; predict factor1; */
data x6; set outdata;
drop prin2 prin3;
run;


proc sql;
	create table years as select distinct
	year, count(*) as N, mean(firm_qtr_bearish_ind_rmean) as firm_qtr_bearish_ind_rmean, mean(bearish_in_roll) as bearish_in_roll,
		mean(pct_bearish) as pct_bearish, mean(firm_qtr_bearish_ind) as firm_qtr_bearish_ind, mean(bearish_in_firm_qtr) as bearish_in_firm_qtr,
		mean(max_bearish_priority_roll) as max_bearish_priority_roll, mean(max_bearish_priority) as max_bearish_priority, mean(n_analysts) as n_analysts, 
		mean(n_analysts_on_call) as n_analysts_on_call, mean(n_analysts_off_call) as n_analysts_off_call, mean(n_bearish) as n_bearish, 
		mean(n_bearish_in) as n_bearish_in, mean(n_bearish_out) as n_bearish_out, mean(n_in) as n_in, mean(n_out) as n_out
	from x6 group by year;
quit;

** add CAR around mostimportantdateutc;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;

data y; set x6 (keep = permno mostimportantdateutc);
run;

%include "modules/Macro Repository.sas";
%get_returns(dsetin=y,dsetout=y2,mkt=vwretd,freq=dsf,n_before=1,n_after=1,event_date = mostimportantdateutc,car_name=car,bhar_name=bhar,n_name=n,std_name=stdret);


proc sql;
	create table x7 as select distinct
	a.*, b.car_m1to_p1 as cc_car, b.bhar_m1to_p1 as cc_bhar, b.stdret_m1to_p1 as cc_stdret
	from x6 as a left join y2 as b
	on a.permno = b.permno
	and a.mostimportantdateutc = b.mostimportantdateutc;
quit;

/* 
proc sort data = x9 nodupkey; by gvkey datadate;
run; 
*/

data perm.engage_class; set x7;
run;
