options errorabend;
libname ibes "../data/raw";
libname match "../data/CIQ";
libname ciq "C:\Users\jaredflake\Dropbox\Research\Data\CIQ_Transcripts\capitaliq_transcripts";
libname perm "../data";

data recdata_4d; set perm.ea_analyst; 
run;

PROC SQL;
    select year, mean(bearish) as bearish, mean(sell) as sell, 
        mean(downgrade) as downgrade, mean(nbuy) as nbuy
    from recdata_4d group by year;
quit;

* I believe unmatched analysts (should not be in the sample at this point) are grouped with the non-participants on the call;
proc sql;
	create table recdata_5 as select distinct
	ticker, permno, mostimportantdateutc, 
    mean(bearish) as firm_qtr_bearish,  
    sum(bearish) as n_bearish, 
    max(n_analysts_on_call) as n_analysts_on_call, count(bearish) as n_analysts, 
    max(bearish_priority) as max_bearish_priority, 
    sum(n_questions_bearish)/sum(bearish) as avg_questions_bearish, 
    sum(n_answers_bearish)/sum(bearish) as avg_answers_bearish, 
    mean(n_answers) as avg_n_answers, mean(n_questions) as avg_n_questions
	from recdata_4d group by ticker, mostimportantdateutc;
quit;

proc sql;
	create table recdata_5_in as select distinct
	ticker, permno, mostimportantdateutc, in_, 
    sum(bearish) as n_bearish,
    mean(bearish) as firm_qtr_in_bearish,
    count(amaskcd) as n_analysts
	from recdata_4d group by ticker, mostimportantdateutc, in_;
quit;

data in_; set recdata_5_in;
if in_ = 1;
run;

data out_; set recdata_5_in;
if in_ = 0;
run;

proc sql;
	create table recdata_6 as select distinct
	a.*, b.firm_qtr_in_bearish as bearish_in_firm_qtr,  
        c.firm_qtr_in_bearish as bearish_out_firm_qtr,
        (b.firm_qtr_in_bearish-c.firm_qtr_in_bearish) as bearish_in_out_dif, 
        b.n_analysts as n_in, c.n_analysts as n_out, 
        b.n_bearish/a.n_bearish as firm_qtr_in_bearish2,
        (b.n_bearish > 0) as firm_qtr_bearish_ind, 
        b.n_bearish as n_bearish_in, c.n_bearish as n_bearish_out
	from recdata_5 as a
	left join in_ as b on a.ticker = b.ticker and a.mostimportantdateutc = b.mostimportantdateutc
	left join out_ as c on a.ticker = c.ticker and a.mostimportantdateutc = c.mostimportantdateutc;
quit;

data recdata_7; set recdata_6;
t_year = year(mostimportantdateutc);
pct_bearish = n_bearish/n_analysts;
if pct_bearish = 0 or pct_bearish = 1 then do;
    bearish_in_firm_qtr = .;
    firm_qtr_in_bearish2 = .;
    firm_qtr_bearish_ind = .;
    max_bearish_priority = .;
end;
run;

proc sort data = recdata_7 nodupkey; by permno mostimportantdateutc;
run;

proc sql;
	select t_year, count(*) as N_Transcripts
	from recdata_7 group by t_year;
quit;

proc sort data = recdata_7 nodupkey; by permno mostimportantdateutc;
run;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data = recdata_7 out = firm_rolling method = none;
	convert bearish_in_firm_qtr=bearish_in_roll / transformout=(movave 12 TRIMLEFT 2) ;
	convert firm_qtr_in_bearish2=firm_qtr_in_bearish2_roll / transformout=(movave 12 TRIMLEFT 2); 
    convert firm_qtr_bearish_ind=firm_qtr_bearish_ind_rmean / transformout=(movave 12 TRIMLEFT 2);
    convert firm_qtr_bearish_ind=firm_qtr_bearish_ind_rmax / transformout=(movmax 12 TRIMLEFT 2);
    convert max_bearish_priority=max_bearish_priority_roll / transformout=(movave 12 TRIMLEFT 2);
	by permno;
run;
proc printto; run;

proc means data = firm_rolling n mean std min p25 p50 p75 max;
var n_analysts n_bearish_in n_bearish_out n_bearish bearish_in_roll firm_qtr_bearish_ind firm_qtr_bearish_ind_rmean 
    max_bearish_priority max_bearish_priority_roll firm_qtr_bearish_ind_rmax
;
run;

/*
Add pcf of firm_qtr_bearish_ind_rmean bearish_in_roll max_bearish_priority_roll
*/

PROC PRINCOMP DATA= firm_rolling OUT=outdata OUTSTAT = stats;
VAR bearish_in_roll firm_qtr_bearish_ind_rmean max_bearish_priority_roll;
RUN;

PROC CORR DATA=outdata;
VAR bearish_in_roll firm_qtr_bearish_ind_rmean max_bearish_priority_roll;
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 outdata2; set outdata;
drop prin2 prin3;
rename prin1 = prin_sir;
run;

proc sort data = outdata2 out = outdata3 nodupkey; by ticker mostimportantdateutc;
run;

%let outfile = firm_qtr_bearish;

data perm.&outfile; set outdata3;
run;
