options errorabend;

/* This code classifies bearish analysts using variations of different combinations of recommendations, price targets, and EPS forecasts */

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


data x; set perm.ea_analyst_2;
data eps; set perm.eps_annual;
data tp; set perm.tp_revisions;
run;


data x2; set x (keep=ticker permno mostimportantdateutc amaskcd rec5_lvl rrec pre_tone bearish);
run;


data ptgdet_1; set ibes.ptgdet;
	where (estcur="USD") and (curr="USD") and usfirm=1
		   and (year(anndats) ge 1999) and horizon="12" and measure="PTG"
		   and (ticker ne " ") and (value ne .) and measure="PTG" 
		   and horizon="12";
run;

**4) IBES EPS Detail History;
data det_epsus_1; set ibes.det_epsus;
	where measure="EPS" /*and (fpi="6" or fpi="7")*/
		  and usfirm=1 and (year(fpedats) ge 1998) and (year(anndats) ge 1998) 
		  and report_curr="USD"
		  and (actual ne .) and (anndats_act ne .)
		  and (ticker ne " ");
		  if value ne . and actual ne . then missed_eps = (actual < value);
		  else missed_eps = .;
run;
*Note: Requiring non-missing actuals drops long-term growth forecasts (fpi="0");


proc sql;
	create table x2a as select distinct
	a.*, b.value as tp_pre, b.anndats as anndats_tp
	from x2 as a left join ptgdet_1 as b
	on a.ticker = b.ticker
	and a.amaskcd = b.amaskcd
	and intnx('month',a.mostimportantdateutc,-12,'s') le b.anndats le a.mostimportantdateutc;
quit;

proc sort data = x2a ; by ticker amaskcd mostimportantdateutc anndats_tp;
run;

data x3; set x2a; by ticker amaskcd mostimportantdateutc;
if last.mostimportantdateutc;
drop anndats_tp;
run;


proc sql; 
	create table x3a as select distinct
	a.*, b.price, b.prdays
	from x3 as a left join ibes.actpsum_epsus (where=(usfirm = 1)) as b
	on a.ticker eq b.ticker
	and (intnx('day',a.mostimportantdateutc,-31) <= b.statpers < a.mostimportantdateutc);
quit;

*Remove duplicates since some extra price data could have been brought in. Randomly remove duplicates since
the difference in price from month to month shouldn't be that different and this (price from IBES) is used to check the actual 
variable we will use (prc from crsp);
proc sort data = x3a nodupkey; by ticker amaskcd mostimportantdateutc;
run;

*Bring in Crsp data;
* Bringin in the split adjusted stock price from CRSP - manually verified that we need to divide by the split factor;
* We keep the last observation that is at least 5 days before the RDQ;
proc sql; 
	create table x3b as select
	a.*, b.date, abs(b.prc)/b.cfacpr as price_crsp
	from x3a a left join crsp.dsf b
	on a.permno eq b.permno
	and intnx('weekday',a.mostimportantdateutc,-10) <= b.date <= intnx('weekday',a.mostimportantdateutc,-5);
quit;


*sort - keep last (last.);
proc sort data=x3b; by ticker amaskcd mostimportantdateutc date;
run;
data x3c; set x3b; by ticker amaskcd mostimportantdateutc date;
if last.mostimportantdateutc;
tp_premium = (tp_pre - price_crsp) / price_crsp;
if tp_pre < price_crsp and tp_pre ne . and price_crsp ne . then tp_low = 1;
else if tp_pre ne . and price_crsp ne . then tp_low = 0;
run;

proc sql;
	create table x4 as select distinct
	a.*, b.value as eps_pre, (b.value/b.actual - 1) as pct_dif, b.missed_eps, b.anndats as anndats_eps
	from x3c as a left join det_epsus_1 as b
	on a.ticker = b.ticker
	and a.amaskcd = b.analys
	and intnx('month',a.mostimportantdateutc,-12,'s') le b.anndats le a.mostimportantdateutc;
quit;

proc sort data = x4 ; by ticker amaskcd mostimportantdateutc anndats_eps;
run;

data x5; set x4; by ticker amaskcd mostimportantdateutc;
if last.mostimportantdateutc;
drop anndats_eps;
run;

proc sql;
	create table x6 as select distinct
	a.*, b.pctchg_eps as pctchg_eps_pre, b.anndats as anndats_eps
	from x5 as a left join eps as b
	on a.ticker = b.ticker
	and a.amaskcd = b.analys
	and intnx('month',a.mostimportantdateutc,-12,'s') le b.anndats le a.mostimportantdateutc;
quit;

proc sort data = x6 ; by ticker amaskcd mostimportantdateutc anndats_eps;
run;

data x7; set x6; by ticker amaskcd mostimportantdateutc;
if last.mostimportantdateutc;
drop anndats_eps;
run;


proc sql;
	create table x8 as select distinct
	a.*, b.pctchg_tp as pctchg_tp_pre, b.anndats as anndats_tp
	from x7 as a left join tp as b
	on a.ticker = b.ticker
	and a.amaskcd = b.amaskcd
	and intnx('month',a.mostimportantdateutc,-12,'s') le b.anndats le a.mostimportantdateutc;
quit;

proc sort data = x8 ; by ticker amaskcd mostimportantdateutc anndats_tp;
run;

data x9; set x8; by ticker amaskcd mostimportantdateutc;
if last.mostimportantdateutc;
drop anndats_tp;
tp_low =  (tp_pre < price_crsp);
if tp_pre = . or price_crsp = . then tp_low = .;
run;


proc sort data=x9; by ticker mostimportantdateutc;
proc rank data=x9 out=x10 groups=3; by ticker mostimportantdateutc;
var eps_pre tp_pre pre_tone pctchg_tp_pre;
ranks Reps Rtp Rtone rchg_tp;
run;

data x11; set x10;
reps = reps - 1;
rtp = rtp - 1;
rtone = rtone - 1;
rchg_tp = rchg_tp - 1;
avg_rank = mean(rrec,reps,rtp);
rel_bearish_avg = (avg_rank < 0 and avg_rank ne .);
if rec5_lvl < 3 and rec5_lvl ne . then sell = 1;
else sell = 0;
if pctchg_tp_pre le 0 and pctchg_tp_pre ne . then tp_down_pre = 1;
else if pctchg_tp_pre ge 0 then tp_down_pre = 0;

if pctchg_tp_pre le -.10 and pctchg_tp_pre ne . then tp_down10_pre = 1;
else if pctchg_tp_pre ge -.10 then tp_down10_pre = 0;

if pctchg_tp_pre le -.20 and pctchg_tp_pre ne . then tp_down20_pre = 1;
else if pctchg_tp_pre ge -.20 then tp_down20_pre = 0;

avg_bearishness = mean(sell,missed_eps,tp_low);
bearish_avg = (avg_bearishness > .5 and avg_bearishness ne .);

if missed_eps = 1 and tp_low = 1 and sell = 1 then bearish_all = 1;
else if missed_eps ne . and tp_low ne . and sell ne . then bearish_all = 0;

bearish_tp_rec = max(tp_low,sell);

bearish_tp_lc = max(tp_low,tp_down10_pre);

bearish_tp_rec_lc = max(tp_low,tp_down10_pre,bearish);

if reps = -1 and rtp = -1 and rrec = -1 then rel_bearish_all = 1;
else if reps ge 0 and rtp ge 0 and rrec ge 0 then rel_bearish_all = 0;

if tp_low = 1 and tp_low ne . then bearish_tp = 1;


else bearish_tp = 0;
run;


proc sql;
	create table x12 as select distinct
	a.*, b.tp_premium, b.tp_low, b.rel_bearish_avg, b.rel_bearish_all, b.reps, b.rtp, b.avg_rank, b.bearish_avg, b.bearish_all, b.bearish_tp_rec, b.bearish_tp, b.rtone,
		b.bearish_tp_lc, b.bearish_tp_rec_lc, b.tp_down_pre, b.tp_down10_pre, b.tp_down20_pre
	from x as a left join x11 as b
	on a.ticker = b.ticker
	and a.amaskcd = b.amaskcd
	and a.mostimportantdateutc = b.mostimportantdateutc
	order by ticker, mostimportantdateutc, amaskcd;
quit;

data perm.ea_analyst_3  ; set x12;
run;


* Aggregate at firm-quarter;
proc sql;
	create table y as select distinct
	ticker, permno, mostimportantdateutc, count(in_) as n_analysts, sum(in_) as n_in, 

			sum(bearish) as n_bearish, sum(bearish*in_) as n_bearish_in, mean(bearish) as pct_bearish,
			max(bearish*in_) as firm_qtr_bearish_ind, sum(bearish*in_)/sum(in_) as bearish_in_firm_qtr, 
			max(bearish*priority) as max_bearish_priority, max(bearish*priority) > .6 as bearish_early,

			sum(rel_bearish_avg) as n_rel_bearish_avg, sum(rel_bearish_avg*in_) as n_rel_bearish_avg_in, mean(rel_bearish_avg) as pct_rel_bearish_avg,
			max(rel_bearish_avg*in_) as firm_qtr_rel_bearish_avg_ind, sum(rel_bearish_avg*in_)/sum(in_) as rel_bearish_avg_in_firm_qtr, 
			max(rel_bearish_avg*priority) as max_rel_bearish_avg_priority, max(rel_bearish_avg*priority) > .6 as rel_bearish_early,

			sum(bearish_avg) as n_bearish_avg, sum(bearish_avg*in_) as n_bearish_avg_in, mean(bearish_avg) as pct_bearish_avg,
			max(bearish_avg*in_) as firm_qtr_bearish_avg_ind, sum(bearish_avg*in_)/sum(in_) as bearish_avg_in_firm_qtr, max(bearish_avg*priority) as max_bearish_avg_priority,
			max(bearish_avg*priority) > .6 as bearish_avg_early,

			sum(bearish_all) as n_bearish_all, sum(bearish_all*in_) as n_bearish_all_in, mean(bearish_all) as pct_bearish_all,
			max(bearish_all*in_) as firm_qtr_bearish_all_ind, sum(bearish_all*in_)/sum(in_) as bearish_all_in_firm_qtr, max(bearish_all*priority) as max_bearish_all_priority,
			max(bearish_all*priority) > .6 as bearish_all_early,

			sum(bearish_tp_rec) as n_bearish_tp_rec, sum(bearish_tp_rec*in_) as n_bearish_tp_rec_in, mean(bearish_tp_rec) as pct_bearish_tp_rec,
			max(bearish_tp_rec*in_) as firm_qtr_bearish_tp_rec_ind, sum(bearish_tp_rec*in_)/sum(in_) as bearish_tp_rec_in_firm_qtr, 
			max(bearish_tp_rec*priority) as max_bearish_tp_rec_priority, max(bearish_tp_rec*priority) > .6 as bearish_tp_rec_early,

			sum(bearish_tp) as n_bearish_tp, sum(bearish_tp*in_) as n_bearish_tp_in, mean(bearish_tp) as pct_bearish_tp,
			max(bearish_tp*in_) as firm_qtr_bearish_tp_ind, sum(bearish_tp*in_)/sum(in_) as bearish_tp_in_firm_qtr, max(bearish_tp*priority) as max_bearish_tp_priority,
			max(bearish_tp*priority) > .6 as bearish_tp_early,

			sum(bearish_tp_lc) as n_bearish_tp_lc, sum(bearish_tp_lc*in_) as n_bearish_tp_lc_in, mean(bearish_tp_lc) as pct_bearish_tp_lc,
			max(bearish_tp_lc*in_) as firm_qtr_bearish_tp_lc_ind, sum(bearish_tp_lc*in_)/sum(in_) as bearish_tp_lc_in_firm_qtr, 
			max(bearish_tp_lc*priority) as max_bearish_tp_lc_priority, max(bearish_tp_lc*priority) > .6 as bearish_tp_lc_early,

			sum(bearish_tp_rec_lc) as n_bearish_tp_rec_lc, sum(bearish_tp_rec_lc*in_) as n_bearish_tp_rec_lc_in, mean(bearish_tp_rec_lc) as pct_bearish_tp_rec_lc,
			max(bearish_tp_rec_lc*in_) as firm_qtr_bearish_tp_rec_lc_ind, sum(bearish_tp_rec_lc*in_)/sum(in_) as bearish_tp_rec_lc_in_firm_qtr, 
			max(bearish_tp_rec_lc*priority) as max_bearish_tp_rec_lc_priority, max(bearish_tp_rec_lc*priority) > .6 as bearish_tp_rec_lc_early

	from x12 group by ticker, mostimportantdateutc;
quit;

data y2; set y;
if pct_bearish = 0 or pct_bearish = 1 then do;
	firm_qtr_bearish_ind = .;
	bearish_in_firm_qtr = .;
	max_bearish_priority = .;
	bearish_early = .;
end;

if pct_bearish_avg = 0 or pct_bearish_avg = 1 then do;
	firm_qtr_bearish_avg_ind = .;
	bearish_avg_in_firm_qtr = .;
	max_bearish_avg_priority = .;
	bearish_avg_early = .;
end;

if pct_bearish_all = 0 or pct_bearish_all = 1 then do;
	firm_qtr_bearish_all_ind = .;
	bearish_all_in_firm_qtr = .;
	max_bearish_all_priority = .;
	bearish_all_early = .;
end;

if pct_bearish_tp_rec = 0 or pct_bearish_tp_rec = 1 then do;
	firm_qtr_bearish_tp_rec_ind = .;
	bearish_tp_rec_in_firm_qtr = .;
	max_bearish_tp_rec_priority = .;
	bearish_tp_rec_early = .;
end;

if pct_bearish_tp = 0 or pct_bearish_tp = 1 then do;
	firm_qtr_bearish_tp_ind = .;
	bearish_tp_in_firm_qtr = .;
	max_bearish_tp_priority = .;
	bearish_tp_early = .;
end;

if pct_bearish_tp_lc = 0 or pct_bearish_tp_lc = 1 then do;
	firm_qtr_bearish_tp_lc_ind = .;
	bearish_tp_lc_in_firm_qtr = .;
	max_bearish_tp_lc_priority = .;
	bearish_tp_lc_early = .;
end;

if pct_bearish_tp_rec_lc = 0 or pct_bearish_tp_rec_lc = 1 then do;
	firm_qtr_bearish_tp_rec_lc_ind = .;
	bearish_tp_rec_lc_in_firm_qtr = .;
	max_bearish_tp_rec_lc_priority = .;
	bearish_tp_rec_lc_early = .;
end;

if firm_qtr_bearish_ind ne . then used = 1;
else used = 0;

if firm_qtr_bearish_avg_ind ne . then used_avg = 1;
else used_avg = 0;

if firm_qtr_bearish_all_ind ne . then used_all = 1;
else used_all = 0;

if firm_qtr_bearish_tp_rec_ind ne . then used_tp_rec = 1;
else used_tp_rec = 0;

if firm_qtr_bearish_tp_ind ne . then used_tp = 1;
else used_tp = 0;

if firm_qtr_bearish_tp_lc_ind ne . then used_tp_lc = 1;
else used_tp_lc = 0;

if firm_qtr_bearish_tp_rec_lc_ind ne . then used_tp_rec_lc = 1;
else used_tp_rec_lc = 0;

run;

proc sort data = y2 nodupkey; by permno mostimportantdateutc;
run;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data = y2 out = y3 method = none;

	convert max_bearish_priority=bearish_priority / transformout=(movave 12 TRIMLEFT 2) ;
	convert firm_qtr_bearish_ind=bearish_engage / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_in_firm_qtr=bearish_engage_2 / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_early=bearish_early_roll / transformout=(movave 12 TRIMLEFT 2) ;
	convert used = n_signals / transformout=(movsum 12 TRIMLEFT 2) ;

	convert max_bearish_avg_priority=bearish_avg_priority / transformout=(movave 12 TRIMLEFT 2) ;
	convert firm_qtr_bearish_avg_ind=bearish_avg_engage / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_avg_in_firm_qtr=bearish_avg_engage_2 / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_avg_early=bearish_avg_early_roll / transformout=(movave 12 TRIMLEFT 2) ;
	convert used_avg = n_signals_avg / transformout=(movsum 12 TRIMLEFT 2) ;

	convert max_bearish_all_priority=bearish_all_priority / transformout=(movave 12 TRIMLEFT 2) ;
	convert firm_qtr_bearish_all_ind=bearish_all_engage / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_all_in_firm_qtr=bearish_all_engage_2 / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_all_early=bearish_all_early_roll / transformout=(movave 12 TRIMLEFT 2) ;
	convert used_all = n_signals_all / transformout=(movsum 12 TRIMLEFT 2) ;

	convert max_bearish_tp_rec_priority=bearish_tp_rec_priority / transformout=(movave 12 TRIMLEFT 2) ;
	convert firm_qtr_bearish_tp_rec_ind=bearish_tp_rec_engage / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_tp_rec_in_firm_qtr=bearish_tp_rec_engage_2 / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_tp_rec_early=bearish_tp_rec_early_roll / transformout=(movave 12 TRIMLEFT 2) ;
	convert used_tp_rec = n_signals_tp_rec / transformout=(movsum 12 TRIMLEFT 2) ;

	convert max_bearish_tp_priority=bearish_tp_priority / transformout=(movave 12 TRIMLEFT 2) ;
	convert firm_qtr_bearish_tp_ind=bearish_tp_engage / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_tp_in_firm_qtr=bearish_tp_engage_2 / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_tp_early=bearish_tp_early_roll / transformout=(movave 12 TRIMLEFT 2) ;
	convert used_tp = n_signals_tp / transformout=(movsum 12 TRIMLEFT 2) ;

	convert max_bearish_tp_lc_priority=bearish_tp_lc_priority / transformout=(movave 12 TRIMLEFT 2) ;
	convert firm_qtr_bearish_tp_lc_ind=bearish_tp_lc_engage / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_tp_lc_in_firm_qtr=bearish_tp_lc_engage_2 / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_tp_lc_early=bearish_tp_lc_early_roll / transformout=(movave 12 TRIMLEFT 2) ;
	convert used_tp_lc = n_signals_tp_lc / transformout=(movsum 12 TRIMLEFT 2) ;

	convert max_bearish_tp_rec_lc_priority=bearish_tp_rec_lc_priority / transformout=(movave 12 TRIMLEFT 2) ;
	convert firm_qtr_bearish_tp_rec_lc_ind=bearish_tp_rec_lc_engage / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_tp_rec_lc_in_firm_qtr=bearish_tp_rec_lc_engage_2 / transformout=(movave 12 TRIMLEFT 2) ;
	convert bearish_tp_rec_lc_early=bearish_tp_rec_lc_early_roll / transformout=(movave 12 TRIMLEFT 2) ;
	convert used_tp_rec_lc = n_signals_tp_rec_lc / transformout=(movsum 12 TRIMLEFT 2) ;

	by permno;
run;
proc printto; run;

PROC PRINCOMP DATA= y3 OUT=y4a OUTSTAT = stats;
VAR bearish_engage bearish_engage_2 bearish_priority;
RUN;

PROC CORR DATA=y4a;
VAR bearish_engage bearish_engage_2 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 y4b; set y4a;
drop prin2 prin3;
rename prin1 = prin_sir;
run;

PROC PRINCOMP DATA= y4b OUT=y4bc OUTSTAT = stats;
VAR firm_qtr_bearish_ind bearish_in_firm_qtr max_bearish_priority;
RUN;

PROC CORR DATA=y4bc;
VAR firm_qtr_bearish_ind bearish_in_firm_qtr 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 y4bd; set y4bc;
drop prin2 prin3;
rename prin1 = prin;
run;

PROC PRINCOMP DATA= y4bd OUT=y5 OUTSTAT = stats;
VAR bearish_avg_engage bearish_avg_engage_2 bearish_avg_priority;
RUN;

PROC CORR DATA=y5;
VAR bearish_avg_engage bearish_avg_engage_2 bearish_avg_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 y6; set y5;
drop prin2 prin3;
rename prin1 = prin_avg;
run;

PROC PRINCOMP DATA= y6 OUT=y7 OUTSTAT = stats;
VAR bearish_all_engage bearish_all_engage_2 bearish_all_priority;
RUN;

PROC CORR DATA=y7;
VAR bearish_all_engage bearish_all_engage_2 bearish_all_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 y8; set y7;
drop prin2 prin3;
rename prin1 = prin_all;
run;

PROC PRINCOMP DATA= y8 OUT=y9 OUTSTAT = stats;
VAR bearish_tp_rec_engage bearish_tp_rec_engage_2 bearish_tp_rec_priority;
RUN;

PROC CORR DATA=y9;
VAR bearish_tp_rec_engage bearish_tp_rec_engage_2 bearish_tp_rec_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 y10; set y9;
drop prin2 prin3;
rename prin1 = prin_tp_rec;
run;

PROC PRINCOMP DATA= y10 OUT=y11 OUTSTAT = stats;
VAR bearish_tp_engage bearish_tp_engage_2 bearish_tp_priority;
RUN;

PROC CORR DATA=y11;
VAR bearish_tp_engage bearish_tp_engage_2 bearish_tp_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 y12; set y11;
drop prin2 prin3;
rename prin1 = prin_tp;
run;

PROC PRINCOMP DATA= y12 OUT=y12a OUTSTAT = stats;
VAR bearish_tp_rec_lc_engage bearish_tp_rec_lc_engage_2 bearish_tp_rec_lc_priority;
RUN;

PROC CORR DATA=y12a;
VAR bearish_tp_rec_lc_engage bearish_tp_rec_lc_engage_2 bearish_tp_rec_lc_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 y12b; set y12a;
drop prin2 prin3;
rename prin1 = prin_tp_rec_lc;
run;

PROC PRINCOMP DATA= y12b OUT=y12c OUTSTAT = stats;
VAR bearish_tp_lc_engage bearish_tp_lc_engage_2 bearish_tp_lc_priority;
RUN;

PROC CORR DATA=y12c;
VAR bearish_tp_lc_engage bearish_tp_lc_engage_2 bearish_tp_lc_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 y12d; set y12c;
drop prin2 prin3;
rename prin1 = prin_tp_lc;
run;

data z; set perm.engage_class ;
run;

proc sql;
	create table z1 as select distinct
	a.*, b.n_analysts as n_analysts_avg, b.n_in as n_in_avg, b.n_signals, b.bearish_early_roll,

		b.n_bearish_avg, b.n_bearish_avg_in, b.firm_qtr_bearish_avg_ind, b.bearish_avg_in_firm_qtr, b.max_bearish_avg_priority, 
		b.pct_bearish_avg, b.bearish_avg_engage, b.bearish_avg_engage_2, b.bearish_avg_priority, b.n_signals_avg, b.prin_avg, b.bearish_avg_early_roll, 

		b.n_bearish_all, b.n_bearish_all_in, b.firm_qtr_bearish_all_ind, b.bearish_all_in_firm_qtr, b.max_bearish_all_priority, 
		b.pct_bearish_all, b.bearish_all_engage, b.bearish_all_engage_2, b.bearish_all_priority, b.n_signals_all, b.prin_all, b.bearish_all_early_roll, 

		b.n_bearish_tp_rec, b.n_bearish_tp_rec_in, b.firm_qtr_bearish_tp_rec_ind, b.bearish_tp_rec_in_firm_qtr, b.max_bearish_tp_rec_priority,
		b.pct_bearish_tp_rec, b.bearish_tp_rec_engage, b.bearish_tp_rec_engage_2, b.bearish_tp_rec_priority, b.n_signals_tp_rec, b.prin_tp_rec, b.bearish_tp_rec_early_roll,

		b.n_bearish_tp, b.n_bearish_tp_in, b.firm_qtr_bearish_tp_ind, b.bearish_tp_in_firm_qtr, b.max_bearish_tp_priority,
		b.pct_bearish_tp, b.bearish_tp_engage, b.bearish_tp_engage_2, b.bearish_tp_priority, b.n_signals_tp, b.prin_tp, b.bearish_tp_early_roll,

		b.n_bearish_tp_rec_lc, b.n_bearish_tp_rec_lc_in, b.firm_qtr_bearish_tp_rec_lc_ind, b.bearish_tp_rec_lc_in_firm_qtr, b.max_bearish_tp_rec_lc_priority,
		b.pct_bearish_tp_rec_lc, b.bearish_tp_rec_lc_engage, b.bearish_tp_rec_lc_engage_2, b.bearish_tp_rec_lc_priority, b.n_signals_tp_rec_lc, b.prin_tp_rec_lc, 
		b.bearish_tp_rec_lc_early_roll,

		b.n_bearish_tp_lc, b.n_bearish_tp_lc_in, b.firm_qtr_bearish_tp_lc_ind, b.bearish_tp_lc_in_firm_qtr, b.max_bearish_tp_lc_priority,
		b.pct_bearish_tp_lc, b.bearish_tp_lc_engage, b.bearish_tp_lc_engage_2, b.bearish_tp_lc_priority, b.n_signals_tp_lc, b.prin_tp_lc, b.bearish_tp_lc_early_roll

	from z as a left join y12d as b
	on a.permno = b.permno
	and a.mostimportantdateutc = b.mostimportantdateutc;
quit;

%let outfile = engage_class_2;
data perm.&outfile ; set z1 ;
run;

proc export data = perm.&outfile
	outfile = "../data/engage_class_2.dta"
	dbms = dta replace;
run;

proc means data = perm.&outfile n mean min p5 p10 p25 p50 p75 p90 p95 max;
var n_analysts n_in n_signals 

/*	bearish_priority bearish_engage bearish_engage_2*/

	n_bearish n_bearish_in max_bearish_priority firm_qtr_bearish_ind max_bearish_priority_roll firm_qtr_bearish_ind_rmean bearish_in_roll
	
	n_bearish_avg n_bearish_avg_in max_bearish_avg_priority firm_qtr_bearish_avg_ind bearish_avg_priority bearish_avg_engage bearish_avg_engage_2

	n_bearish_all n_bearish_all_in max_bearish_all_priority firm_qtr_bearish_all_ind bearish_all_priority bearish_all_engage bearish_all_engage_2

	n_bearish_tp_rec n_bearish_tp_rec_in max_bearish_tp_rec_priority firm_qtr_bearish_tp_rec_ind bearish_tp_rec_priority bearish_tp_rec_engage bearish_tp_rec_engage_2

	n_bearish_tp n_bearish_tp_in max_bearish_tp_priority firm_qtr_bearish_tp_ind bearish_tp_priority bearish_tp_engage bearish_tp_engage_2

	n_bearish_tp_lc n_bearish_tp_lc_in max_bearish_tp_lc_priority firm_qtr_bearish_tp_lc_ind bearish_tp_lc_priority 
	bearish_tp_lc_engage bearish_tp_lc_engage_2

	n_bearish_tp_rec_lc n_bearish_tp_rec_lc_in max_bearish_tp_rec_lc_priority firm_qtr_bearish_tp_rec_lc_ind bearish_tp_rec_lc_priority 
	bearish_tp_rec_lc_engage bearish_tp_rec_lc_engage_2;

run;
