options errorabend;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
/***Bearish analysts and difficult questions***/
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;

/**Step 1: Pull IBES Recommendation data**/
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
/***Import dataset folders;*/
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_1; set perm.rec_obs;
where (amaskcd > 0);
run;

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

data recdata_1a; set recdata_1;
where (permno ne .) /*and permno in (27828,10107,14593) */;
run;
proc sort data = recdata_1a nodupkey; by ticker amaskcd datetime_1;
run;
*recdata_1 amaskcd has format z8. and informat 8.;
*glz4 amaskcd has format of ;
* By requiring amaskcd to have nonmissing, it requires there to be a valid IBES-CIQ match;
data glz4; set perm.glz_w_ibes;
    where (permno ne .) /*and (permno in (27828,10107,14593))*/ and
        word_count_q gt 5 and (amaskcd ne .);
run;

proc sort data = glz4 out= glz_temp (keep = permno mostimportantdateutc) nodupkey; by permno mostimportantdateutc;
run;

* Create panel data set of analyst-firm-qtrs where the analyst has a recommendation observation in ;
*    (1) the year prior to the earnings announcement or ;
*    (2) a IBES announce date (anndats) prior to the earnings announcement and a review date (revdats) after the earnings announcement ;
* to ensure I keep the most recent valid stock recommendation for each analyst prior to the firm's earnings announcement;
proc sql;
	create table recdata_2a as select distinct
	a.*, b.mostimportantdateutc, (b.mostimportantdateutc ne .) as priority
	from recdata_1 as a left join glz_temp as b
	on a.permno = b.permno
	and b.mostimportantdateutc - 360 le a.anndats lt b.mostimportantdateutc;
quit;

proc sql;
	create table recdata_2b as select distinct
	a.*, b.mostimportantdateutc, (b.mostimportantdateutc ne .)*2 as priority
	from recdata_1 as a left join glz_temp as b
	on a.permno = b.permno
	and a.anndats lt b.mostimportantdateutc le a.revdats;
quit;

* Combine the resulting datasets from the two different merges giving the within year recommendations priority;
data recdata_2; set recdata_2a recdata_2b;
run;

proc sort data = recdata_2; by ticker amaskcd datetime_1 mostimportantdateutc priority;
run;

* Keep an observation for each earnings call and all recommendations within 360 days prior to the call or a review date after the call;
proc sort data = recdata_2 nodupkey; by ticker amaskcd datetime_1 mostimportantdateutc;
run;

* Drop observations that are not within one year of an earnings transcript or have a valid review data after the EA date;
data recdata_2; set recdata_2;
where mostimportantdateutc ne .;
drop priority;
run;

proc sort data = recdata_2 nodupkey; by ticker mostimportantdateutc amaskcd datetime_1;
run;

* Keep the recommendaiton closest to the conference call and;
* Classify analysts ratings for the firm ;
**  -bearish = sell ratings or downgrades;
**  -sell = sell rating;
**  -downgrade = most recent recommendation was a downgrade and was in the prior year;
data recdata_3; set recdata_2; by ticker mostimportantdateutc amaskcd datetime_1;
if last.amaskcd;
if amaskcd ne .;
if rec5_lvl in (1,2) then do;
    bearish = 1;
    sell = 1;
end;
else do;
    bearish = 0;
    sell=0;
end;
if rec5_lvl = . then do;
    bearish = .;
    sell = .;
end;
* Ensure the downgrade was recent enough for the firm to determine the analyst is unfavorable;
if mostimportantdateutc - anndats le 360 and downgrade_5 = 1 
    then do;
        bearish = 1;
        downgrade = 1;
    end;
    else do;
        downgrade = 0;
    end;
* If the analyst has a buy rec, not bearish;
if rec5_lvl >= 4 then bearish = 0;
if rec5_lvl < 4 then nbuy = 1;
else nbuy = 0;
if rec5_lvl = . then nbuy = .;
year = year(mostimportantdateutc);
qtr = qtr(mostimportantdateutc);
run;

/* 
Add in analyst EPS forecasts for this quarter
*/
 *Get IBES Data
IBES.det_epsus
-	Ticker, cusip, anndats_act (rdq), anndats (>anndats_act but < anndats_act + 7) forecast date Ã¢â‚¬â€œ within 7 days of anndats_act (rdq)), 
analys, fpi (where fpi = Ã¢â‚¬Å“1Ã¢â‚¬Â), measure (where = Ã¢â‚¬Å“EPSÃ¢â‚¬Â), value (rename to fcstEPS), USfirm = 1, fpedats (datadate), actual (rename EPS), ;
data x; set ibes.det_epsus (keep = ticker cusip anndats_act anndats anntims analys estimator fpi measure value usfirm fpedats actual);						
where /*ticker in ("AA3E","AAPL","AIR","ALCO","ALEX","HWP","MSFT","SNO") and*/ (year(fpedats) ge 2000) and
		(fpi in ("6","1")) and (measure = "EPS") and (usfirm = 1);
rename  value = fcstEPS actual = EPS;
drop measure usfirm;
run;

* Keep last forecast for each analyst - if there is both annual and quarterly forecast for q4, keep quarterly forecast;
proc sort data=x out=x; by ticker estimator analys fpedats anndats anntims fpi;
where (eps ne .) and (fcstEPS ne .);
run;

data eps; set x; by ticker estimator analys fpedats;
* Label forecast as missed or beat;
if (EPS >= fcstEPS) then missed = 0;
else missed = 1;
if last.fpedats;
drop anntims ;
run;

proc sql;
	create table recdata_3a as select distinct
	a.*, b.missed, (b.missed*a.nbuy) as nbuy_missed, b.anndats as eps_anndats
	from recdata_3 as a left join EPS as b
	on a.ticker = b.ticker
	and a.amaskcd = b.analys
/* Merge in forecasts with an EA date close to the conference call*/
	and a.mostimportantdateutc-15 <= b.anndats_act <= a.mostimportantdateutc+5;
quit;

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

data recdata_3a; set recdata_3a; by ticker mostimportantdateutc amaskcd;
if last.amaskcd;
drop eps_anndats;
run;

* classify analysts as either participating or not - those with a matching amaskcd participate in the call;
** Analysts without valid matches are thrown out in the next steps; 
proc sql;
	create table recdata_4 as select distinct
	a.*, (b.amaskcd ne .) as in_
	from recdata_3a as a left join glz4 as b
	on a.permno = b.permno
	and a.amaskcd = b.amaskcd
	and a.mostimportantdateutc = b.mostimportantdateutc;
quit;

proc sort data = recdata_4 nodupkey; by ticker mostimportantdateutc amaskcd;
run;

proc sort data=recdata_4; by ticker mostimportantdateutc;
proc rank data=recdata_4 out=recdata_4 groups=3; by ticker mostimportantdateutc;
var rec5_lvl;
ranks Rrec;
run;

data recdata_4; set recdata_4;
Rrec = Rrec-1;
if rrec < 0 then bearish_rel = 1; else bearish_rel = 0;
if rrec = . then bearish_rel = .;
run;

* Filter out IBES analysts with no valid match to CIQ;
* This ensures I don't misclassify participating analysts as those who do not participate;
* Also helps ensure the analysts in the sample participate in enough calls to match;
proc sort data = match.ciq_ibesbrokertranslation out = ciq_ibes_broker nodupkey; by estimid;
where estimid ne "";
run;

proc sort data = match.ciq_ibesanalysttranslation out = ciq_ibes_analyst nodupkey; by amaskcd;
where amaskcd ne .;
run;

proc sql;
    create table recdata_4a as select distinct
    a.*, (b.companyofperson ne "") as bullet_broker
    from recdata_4 as a left join ciq_ibes_broker as b
    on a.estimid = b.estimid;
quit;

proc sql;
    create table recdata_4aa as select distinct
    a.*, (b.transcriptpersonid ne .) as bullet_analyst
    from recdata_4a as a left join ciq_ibes_analyst as b
    on a.amaskcd = b.amaskcd;
quit;

data recdata_4b; set recdata_4aa;
where bullet_analyst ne 0;
drop bullet_analyst bullet_broker;
run;
proc sort data = recdata_4b nodupkey; by ticker mostimportantdateutc amaskcd;
run;

data perm.analysts_classifcation; set recdata_4b;
run;

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

/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Calculate bearish priority on each call
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
proc sql;
    create table glz4b as select distinct
    a.*, b.bearish, b.sell, b.downgrade, b.nbuy, b.nbuy_missed, b.anndats, b.rec5_lvl
    from glz4 as a left join recdata_4b as b
    on a.permno = b.permno
    and a.mostimportantdateutc = b.mostimportantdateutc
    and a.amaskcd = b.amaskcd
    ;
quit;

data glz4c; set glz4b;
where bearish ne .;
run;

proc sort data = glz4c out = glz4d nodupkey; by transcriptid transcriptcomponentid_q transcriptcomponentid_ans transcriptpersonid_q;
run;

* Count the number of questions asked and answers given by each analyst on the call;
proc sql;
    create table glz5 as select
    *, count(transcriptcomponentid_ans) as n_answers, count(distinct transcriptcomponentid_q) as n_questions
    from glz4d group by transcriptid, transcriptpersonid_q
    order by transcriptid, transcriptcomponentid_q, transcriptcomponentid_ans, transcriptpersonid_q
    ;
quit;


data glz6; set glz5; by transcriptid transcriptcomponentid_q transcriptcomponentid_ans transcriptpersonid_q;
retain q_order ;
lag_transcriptpersonid_q = lag(transcriptpersonid_q);
if first.transcriptid then do;
    q_order = 0;
    p_order = 0;
    lag_transcriptpersonid_q = .;
end;
if first.transcriptpersonid_q and transcriptpersonid_q ne lag_transcriptpersonid_q then p_order + 1;
if first.transcriptpersonid_q  then q_order + 1;
drop lag_transcriptpersonid_q;
run;

* This dataset has each analyst's question, analysts' favorability, and other characteristics;
data perm.cc_glz_analysts; set glz6;
run;

proc sql;
    create table priority as select distinct
    amaskcd, permno, mostimportantdateutc, n_answers, n_questions, (n_answers/n_questions) as mean_answers, 
    p_order, max(p_order) as n_analysts
    from glz6 group by permno, mostimportantdateutc 
    ;
quit;

data priority; set priority;
/* reverse the order and scale by total so that highest priority has 1 and lowest priority is just above 0*/
priority = (-p_order+n_analysts+1)/n_analysts ;
run;

proc sql;
    create table priority2 as select distinct
    a.*, b.bearish*a.priority as bearish_priority, b.sell*a.priority as sell_priority, 
        b.downgrade*a.priority as downgrade_priority, b.nbuy*a.priority as nbuy_priority, 
        b.nbuy_missed*a.priority as nb_m_priority, 
    a.n_answers*b.bearish as n_answers_bearish, a.n_answers*b.sell as n_answers_sell, 
        a.n_answers*b.downgrade as n_answers_downgrade, a.n_answers*b.nbuy as n_answers_nbuy,
        a.n_answers*b.nbuy_missed as n_answers_nbuy_missed,
    a.n_questions*b.bearish as n_questions_bearish, a.n_questions*b.sell as n_questions_sell, 
        a.n_questions*b.downgrade as n_questions_downgrade, a.n_questions*b.nbuy as n_questions_nbuy, 
        a.n_questions*b.nbuy_missed as n_questions_nbuy_missed
    from priority as a left join recdata_4b as b
    on a.permno = b.permno
    and a.mostimportantdateutc = b.mostimportantdateutc
    and a.amaskcd = b.amaskcd
    ;
quit;

proc sql;
    create table recdata_4c as select distinct
    a.*, b.priority, b.bearish_priority, b.n_answers_bearish, b.n_questions_bearish, 
                        b.sell_priority, b.n_answers_sell, b.n_questions_sell, 
                        b.downgrade_priority, b.n_answers_downgrade, b.n_questions_downgrade, 
                        b.nbuy_priority, b.n_answers_nbuy, b.n_questions_nbuy,
                        b.nb_m_priority, b.n_answers_nbuy_missed, b.n_questions_nbuy_missed,
        b.n_answers, b.n_questions, b.n_analysts as n_analysts_on_call
    from recdata_4b as a left join priority2 as b
    on a.permno = b.permno
    and a.mostimportantdateutc = b.mostimportantdateutc
    and a.amaskcd = b.amaskcd;
quit;

proc sort data = recdata_4c nodupkey; by ticker mostimportantdateutc amaskcd;
run;

data perm.ea_analyst; set recdata_4c;
run;
