options errorabend;

/* This code merges analyst control variables to analyst-transcript dataset and merges in word count of answers */

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

* Read in main dataset;
data x; set perm.ea_analyst_3;
/*where ticker in ('MSFT', 'SNO', 'AAPL');*/
ccyear = year(mostimportantdateutc);
ccqtr = qtr(mostimportantdateutc);
run;

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


data eps; set ibes.det_epsus
	(keep = ticker estimator analys usfirm anndats anntims anndats_act fpi measure fpedats actual value);
	where fpi in ("1","2","6","7","8","9") and measure = "EPS" and actual ne . and value ne .
/*	and ticker in ('MSFT', 'SNO', 'AAPL')*/
;
	days = anndats_act - anndats;
	afe = abs(value - actual);
	annyear = year(anndats);
	annqtr = qtr(anndats);
	if days > 0;
	fpeqtr = qtr(fpedats);
	fpeyear = year(fpedats);
	fpeqtr_tp1 = fpeqtr+1;
	fpeyear_tp1 = fpeyear;
	if fpeqtr = 4 then do;
		fpeqtr_tp1 = 1;
		fpeyear_tp1 = fpeyear + 1;
	end;
	drop usfirm measure;
run;
*26M obs;

*Keep one obs per analyst-broker-firm-date and count # forecasts;
proc sort data=eps;
	by ticker analys fpedats descending anndats descending anntims estimator;
run;
*1.5M obs;
proc sort data=eps out=eps2 nodupkey;
	by ticker analys anndats fpi;
	where fpi in ("6","7","8","9");
run;

proc sql;
	create table forFreq as select distinct
	ticker, analys, annyear, count(value) as forFreq
	from eps2 group by ticker, analys, annyear;
quit;

/* Merge in forecast frequency of last year - # qtrly forecasts in most recently completed year */
proc sql;
        create table x1 as select distinct
        a.*, b.forFreq
        from x as a left join forFreq as b
        on a.ticker = b.ticker
        and a.amaskcd = b.analys
        and a.ccyear = b.annyear+1;
quit;

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

data x1a; set x1;
if forFreq = . then forFreq = 0;
lnForFreq = log(1+forFreq);
run;

* Merge prior accuracy by keeping the most recent observations of accuracy for the prior quarter or year. ;
**Pull EPS forecasts;
data acc; set eps;
	where fpi in ("1","6") 
;
run;
*26M obs;

proc sort data = acc; by ticker analys fpedats fpi days descending anntims;
run;

data acc2; set acc; by ticker analys fpedats fpi;
if first.fpedats;
run;

/* Calculate relative forecast accuracy*/
proc sql;
	create table acc3 as select distinct
	ticker, analys, fpedats, fpi, anndats_act, anndats, afe, (max(afe) - afe)/(max(afe)-min(afe)) as rel_acc, days
	from acc2 group by ticker, fpedats, fpi
	order by ticker, fpedats, analys;
quit;

proc sql;
        create table x2 as select distinct
        a.*, b.rel_acc as priorAcc, b.anndats_act, (a.mostimportantdateutc - b.anndats_act) as days_b4_cc, b.fpedats, b.fpi
        from x1a as a left join acc3 as b
        on a.ticker = b.ticker
        and a.amaskcd = b.analys
        and intnx('year',a.datadate,-1,'s') le b.fpedats lt a.datadate;
quit;

/* keep most recent forecast for the previous fiscal period. Keep the one-quarter ahead forecast (fpi = 6). If no quarter-ahead forecast, keep one year-ahead forecast */
proc sort data = x2; by ticker mostimportantdateutc amaskcd descending fpedats descending fpi;
proc sort data = x2 nodupkey; by ticker mostimportantdateutc amaskcd descending fpedats ;
proc sort data = x2 out = x3 (drop=fpi) nodupkey; by ticker mostimportantdateutc amaskcd;
run;

/*
proc print data = x3 (obs=30);
var ticker amaskcd mostimportantdateutc datadate anndats_act fpedats priorAcc;
run;
*/

/* CCUser - For analyst i following firm j at fiscal quarter q, equals the total
number of conference calls (excluding firm j) in which analyst i
participated during the calendar quarter containing fiscal
quarter for firm j. */
proc sql;
	create table x4_temp as select 
	a.amaskcd, a.ticker, a.ccyear, a.ccqtr, b.ticker as ticker_other, b.in_ = 1 as CCuser
	from x3 as a left join x3 as b
	on a.amaskcd = b.amaskcd
	and a.ticker ne b.ticker
	and a.ccyear = b.ccyear 
	and a.ccqtr = b.ccqtr;
quit;

proc sql;
	create table x4_temp2 as select distinct
	amaskcd, ticker, ccyear, ccqtr, sum(CCuser) as CCuser
	from x4_temp group by amaskcd, ticker, ccyear, ccqtr;
quit;

proc sql;
	create table x4 as select distinct
	a.*, b.CCuser
	from x3 as a left join x4_temp2 as b
	on a.amaskcd = b.amaskcd
	and a.ticker = b.ticker
	and a.ccyear = b.ccyear
	and a.ccqtr = b.ccqtr;
quit;

proc sort data = x4 nodupkey; by ticker amaskcd mostimportantdateutc;
run;
/* PriorParticipate - Prior conference call participation measured as 1 if the analyst was
identified as asking a question on any of the firm�s prior
conference calls in the sample, and 0 otherwise. */
data x5;
   set x4;
   by ticker amaskcd mostimportantdateutc;

   retain PriorParticipate 0;

   if first.amaskcd then PriorParticipate = 0; /* Reset flag for each new analyst */
   
   /* If the analyst participates and it's their first participation, set PriorParticipate to 0 */
   if in_ = 1 and PriorParticipate = 0 then do;
      output; /* Output row with PriorParticipate = 0 for the first participation */
      PriorParticipate = 1; /* Set PriorParticipate to 1 for all future calls */
   end;
   else output; /* Output row with PriorParticipate = 1 for all subsequent calls */
run;

proc sort data = eps out = eps2b (keep=analys ticker annyear) nodupkey; by analys ticker annyear;
run;
/* Count the number of industries */
proc sql;
	create table inds1 as select distinct
	a.*, b.permno
	from eps2b as a left join ibes.Ibcrsphist as b
	on a.ticker = b.ticker
	and (year(b.sdate) le a.annyear or b.sdate = .B)
	and (a.annyear le year(b.edate) or b.edate = .B);
quit;

data inds2; set inds1;
where permno ne .;
run;

*Get Historical SIC code from CRSP;
proc sql; 
	create table inds3 as select distinct
	a.*, b.siccd, substr(put(b.siccd, 4.), 1, 2) as sic2
	from inds2 as a left join crsp.msenames as b
	on (a.permno eq b.permno) 
	and year(b.namedt) le a.annyear le year(b.NAMEENDT);
quit;

proc sort data = inds3 out = inds4 (keep=analys sic2 annyear) nodupkey; by analys sic2 annyear;
run;

proc sql;
        create table inds5 as select distinct
        analys, annyear, count(distinct sic2) as n_inds
        from inds4 group by analys, annyear;
quit;

/* merge to main dataset */
proc sql;
        create table x6 as select distinct
        a.*, b.n_inds
        from x5 as a left join inds5 as b
        on a.amaskcd = b.analys
        and a.ccyear = b.annyear+1;
quit;


/* Calculate numanalystbroker */
* Numfirmcov and numanalystbroker were mistakenly merged on year = annyear in previous code -
* This code changes that to recalculate those variables correctly. If missing, then use the 
	numfirmscov and numanalystbroker from the same year as the CC date;
* # of analysts forecasting for the broker in the previous calendar year;
* Find unique analysts for each broker in each year;
proc sort data = eps out = cb nodupkey; by estimator analys annyear;
run;
*count # of analysts for each broker in each year;
proc sql;
	create table numanalystbroker as select distinct
	estimator, annyear, count(distinct analys) as numanalystbroker
	from cb group by estimator, annyear;
quit;
proc sort data =numanalystbroker nodupkey; by estimator annyear;
run;

* Merge to x6;
proc sql;
	create table x6b as select distinct
	a.*, b.numanalystbroker as numanalystbroker2
	from x6 as a left join numanalystbroker as b
	on a.estimator = b.estimator
	and a.year = b.annyear+1 ;
quit;

data x6c; set x6b ;
if numanalystbroker2 = . then numanalystbroker2 = numanalystbroker;
drop numanalystbroker;
rename numanalystbroker2 = numanalystbroker;
run;

/* Calculate number of firms covering */
* # of firms the analyst forecasts for in the previous calendar year;
* Find unique firms for each analyst in each year;
proc sort data = eps out = cf nodupkey; by analys annyear ticker;
run;
*count # of firms for each analyst in each year;
proc sql;
	create table numfirmscov as select distinct
	analys, annyear, count(distinct ticker) as numfirmscov
	from cf group by analys, annyear;
quit;

proc sort data = x6c out=fc nodupkey; by ticker amaskcd year;
run;

proc sql;
	create table nfc as select distinct
	amaskcd, year, count(distinct ticker) as nfc
	from fc group by amaskcd, year;
quit;

* Merge to x6b;
proc sql;
	create table x6d as select distinct
	a.*, b.numfirmscov as numfirmscov2
	from x6c as a left join numfirmscov as b
	on a.amaskcd = b.analys
	and a.year = b.annyear+1 ;
quit;

proc sql;
	create table x6e as select distinct
	a.*, b.nfc 
	from x6d as a left join nfc as b
	on a.amaskcd = b.amaskcd
	and a.year = b.year+1 ;
quit;


data x6f; set x6e;
if numfirmscov2 = . then numfirmscov2 = nfc;
if numfirmscov2 = . then numfirmscov2 = numfirmscov;
drop numfirmscov nfc;
rename numfirmscov2 = numfirmscov;
run;

proc sort data = eps out = analysts nodupkey; by ticker analys fpedats;
run;

proc sql;
	create table n_an as select distinct
	ticker, fpedats, count(distinct analys) as n_analysts
	from analysts group by ticker, fpedats;
quit;

proc sql;
	create table x7 as select distinct
	a.*, b.n_analysts
	from x6f as a left join n_an as b
	on a.ticker = b.ticker
	and intnx('year',a.datadate,-1,'s') le b.fpedats le a.datadate;
quit;

proc sort data = x7; by ticker mostimportantdateutc amaskcd descending fpedats;
proc sort data = x7 out = x8 nodupkey; by ticker mostimportantdateutc amaskcd;
run;

proc sql;
	create table firmexp as select distinct
	ticker, analys, min(anndats) as first_date
	from ibes.det_epsus group by ticker, analys;
quit;

proc sql;
	create table genexp as select distinct
	analys, min(anndats) as first_date
	from ibes.det_epsus group by analys;
quit;

proc sql;
	create table x8b as select distinct
	a.*, (a.mostimportantdateutc - b.first_date)/365 as firmexp2
	from x8 as a left join firmexp as b
	on a.ticker = b.ticker
	and a.amaskcd = b.analys;
quit;
proc sql;
	create table x8b as select distinct
	a.*, (a.mostimportantdateutc - b.first_date)/365 as genexp2
	from x8b as a left join genexp as b
	on a.amaskcd = b.analys;
quit;

data x8c; set x8b;
firmexp3 = firmexp2;
if firmexp3 = . then firmexp3 = firmexp;
genexp3 = genexp2;
if genexp3 = . then genexp3 = genexp;
drop firmexp firmexp2 genexp genexp2;
rename firmexp3=firmexp genexp3=genexp;
run;

/* Calculate change in variables */
proc sort data = x8c; by ticker amaskcd mostimportantdateutc;
run;

data x9; set x8c; by ticker amaskcd mostimportantdateutc;
participate_tm1 = lag(in_);
rec5_lvl_tm1 = lag(rec5_lvl);
n_analysts_tm1 = lag(n_analysts);
mostimportantdateutc_tm1 = lag(mostimportantdateutc);
bearish_tm1 = lag(bearish);
if first.amaskcd then do;
	participate_tm1 = .;
	rec5_lvl_tm1 = .;
	n_analysts_tm1 = .;
	mostimportantdateutc_tm1 = .;
	bearish_tm1 = .;
end;
chg_participate = in_ - participate_tm1;
chg_rec5_lvl = rec5_lvl - rec5_lvl_tm1;
chg_n_analysts = n_analysts - n_analysts_tm1;
chg_bearish = bearish - bearish_tm1;
format mostimportantdateutc_tm1 yymmddn8.;
chg_upgrade = 0;
if chg_rec5_lvl > 0 then chg_upgrade = 1;
chg_downgrade = 0;
if chg_rec5_lvl < 0 then chg_downgrade = 1;
run;

/* Add Analyst All-Star Data directly instead of using the analyst controls dataset. The analyst controls dataset is missing some analysts.*/
**Import All-Star data from Excel (1983-2016);
*All-star data from Lian Fen;
%let allstar_path = ../data/raw;
proc import out=allstar_1983_2009
			datafile= "&allstar_path.\II Rankings 1983-2014"
			dbms=xlsx replace;
			sheet="1983-2009";
			getnames=yes;
run;
*9k obs from 1983-2009;
*NOTE: Appears that we do not have data for 2010;

*All-Star data from Mark B.;
proc import out=allstar_2011_2016
			datafile= "&allstar_path.\new all star import v3"
			dbms=xlsx replace;
			sheet="Sheet 1";
			getnames=yes;
run;
*2k obs from 2011-2016;

*Keep and rename relevant variables;
data allstar_1983_2009_1; set allstar_1983_2009;
	where analystid ne .;
	rename year = allstar_year;
	rename highestrank = allstar_rank;
run;

data allstar_2011_2016_1; set allstar_2011_2016
	(keep = year score analyst_code);
run;

data allstar_2011_2016_2; set allstar_2011_2016_1;
	where analyst_code ne .;
	rename analyst_code = analystid;
	rename year = allstar_year;
	rename score = allstar_rank;
run;

**Stack All-Star datasets from 1983-2009 & 2011-2016;
data allstar_1983_2016; 
	set allstar_1983_2009_1 allstar_2011_2016_2;
	if allstar_rank ne . then star_analyst=1;
		else if allstar_rank=. then star_analyst=0;	
run;
*10,745 obs;
*Note: Runner-Ups included as all-star analysts (following Loh and Stulz 2011, for example);

*Create variable for year AFTER All-Star year;
data allstar_1983_2016_1; set allstar_1983_2016;
	allstar_year_tp1 = (allstar_year + 1);
	allstar_rank_tp1 = allstar_rank;
	allstar_year_tp2 = (allstar_year + 2);
	allstar_rank_tp2 = allstar_rank;
	if star_analyst=1 then star_analyst_tp1=1;
		else star_analyst_tp1=0;
	if star_analyst=1 then star_analyst_tp2=1;
		else star_analyst_tp2=0;
run;

*Keep one obs per analyst-year-dummy (drops if analyst has multiple awards in same year);
proc sort data=allstar_1983_2016_1 out=allstar_1983_2016_2 nodupkey;
	by analystid allstar_year star_analyst;
run;
*Drops from 10,745 obs to 10,560 obs;

* extend allstart_year_tp1 from 2010 to 2011 to interpolate missing 2010 all star data;
data y2011 ; set allstar_1983_2016_2;
if allstar_year_tp1 = 2010;
allstar_year_tp1 = 2011;
run;

* add 2011 back to main set;
data allstar_1983_2016_3; set allstar_1983_2016_2 y2011;
run;

*Keep one obs per analyst-year-dummy (drops if analyst has multiple awards in same year);
proc sort data=allstar_1983_2016_3 out=allstar_1983_2016_3 nodupkey;
	by analystid allstar_year_tp1 star_analyst_tp1;
run;
*Drops from 10,745 obs to 10,560 obs;

**Merge all-star data to merged analyst control dataset;
	*Merge on year AFTER All-Star award (All-Star announced in October of a given year - e.g., Oct. 2005);
proc sql;
	create table x9b as select distinct
	a.*, b.star_analyst_tp1
	from x9 as a left join allstar_1983_2016_3 as b
	on a.amaskcd = b.analystid
	and a.year = b.allstar_year_tp1; /* extend window to fill in missing years (2010/2011) */
quit;

**Create All-Star dummy: equal to one if analyst is ranked year of OR year after; 
data x9c; set x9b;
	if star_analyst_tp1=1 then allstar_dummy2=1;
		else allstar_dummy2=0;
	drop star_analyst_tp1;
	if year > 2017 then do;
		allstar_dummy2 = .;
		allstar_dummy = .;
	end;
	drop allstar_dummy;
	rename allstar_dummy2 = allstar_dummy;
run;

/*
proc print data = x9 (obs=600);
var ticker amaskcd mostimportantdateutc mostimportantdateutc_tm1 n_analysts n_analysts_tm1 chg_n_analysts in_ participate_tm1 chg_participate rec5_lvl rec5_lvl_tm1 chg_rec5_lvl;
run;

proc means data = x9 n mean min p10 p25 p50 p75 p90 p99 max;
var in_ n_analysts allstar_dummy prioracc firmexp genexp n_inds forfreq numanalystbroker numfirmscov CCuser PriorParticipate days_valid_rec days_btwn_rec chg_upgrade chg_downgrade
		chg_participate chg_rec5_lvl chg_n_analysts;
run;
*/

proc sort data = x9c out = x10 nodupkey; by ticker mostimportantdateutc amaskcd;
run;

*********;
* Add answer length;
********;
data wc; set perm.glz_w_ibes;
where amaskcd ne . and permno ne .;
run;

proc sql;
	create table wc2 as select distinct
	transcriptid, permno, amaskcd, mostimportantdateutc, 
	sum(word_count_ans) as word_count_ans, sum(word_count_ans_scaled) as word_count_ans_scaled,
	mean(word_count_qa) as word_count_qa, mean(qa_min) as qa_min
	from wc group by transcriptid, permno, amaskcd, mostimportantdateutc;
quit;

proc sql;
	create table x11 as select distinct
	a.*, b.word_count_ans, b.word_count_ans_scaled
	from x10 as a left join wc2 as b
	on a.permno = b.permno
	and a.mostimportantdateutc = b.mostimportantdateutc 
	and a.amaskcd = b.amaskcd;
quit;

proc sql;
	create table cc as select distinct
	transcriptid, permno, mostimportantdateutc, mean(qa_min) as qa_min, mean(word_count_qa) as word_count_qa /* These values are the same for every observation in the same transcript */
	from wc2 group by transcriptid, permno, mostimportantdateutc;
quit;

proc sql;
	create table x12 as select distinct
	a.*, b.word_count_qa, b.qa_min
	from x11 as a left join cc as b
	on a.permno = b.permno
	and a.mostimportantdateutc = b.mostimportantdateutc ;
quit;

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

%let outfile = ea_participation;
data perm.&outfile.; set x12;
run;

proc export data=x12
	outfile= "../data/&outfile..dta"
	dbms=stata
	replace;
run;

