options errorabend;
* Path and library to save data;
%let path = ../data/raw;
libname ibes '../data/raw';
libname perm '../data';
%include "modules/Macro Repository.sas";
%let allstar_path = ../data/raw;

/**Part 1: Analyst-level (except All-Star) and Broker-level control variables**/
**Pull EPS forecasts;
data analyst_controls_1; set ibes.det_epsus
	(keep = ticker estimator analys usfirm anndats);
	where usfirm=1;
	annyear = year(anndats);
	/*ann_year_qtr = yyq(year(anndats),qtr(anndats));
	format ann_year_qtr yyq6.;*/
	drop usfirm anndats;
run;

*Keep one obs per analyst-broker-firm-year;
proc sort data=analyst_controls_1 out=analyst_controls_2 nodupkey;
	by ticker analys estimator annyear;
run;

**Create FIRM experience variable (analyst-firm-year level, NOT including bank);
proc sort data=analyst_controls_2;
	by analys ticker annyear;
run;

data firmexp; set analyst_controls_2;
	by analys ticker annyear;
	if first.ticker;
run;
*Note: Not including broker ID (since analyst can follow a firm at a previous broker employer);

proc sql;
	create table analyst_controls_3 as select unique
	a.*, b.annyear as firstfirmyear
	from analyst_controls_2 as a left join firmexp as b
	on a.ticker = b.ticker
	and a.analys = b.analys;
quit;

**Create GENERAL experience variable (analyst-year level, NOT including bank);
proc sort data=analyst_controls_3;
	by analys annyear;
run;

data genexp; set analyst_controls_3;
	by analys annyear;
	if first.analys;
run;
*28k obs;

proc sql;
	create table analyst_controls_4 as select unique
	a.*, b.annyear as firstgenyear
	from analyst_controls_3 as a left join genexp as b
	on a.analys = b.analys;
quit; 

data analyst_controls_5; set analyst_controls_4;
	firmexp = annyear - firstfirmyear;
	genexp = annyear - firstgenyear;
run;


**Create BANK experience variable (analyst-bank-year level);
proc sort data=analyst_controls_5;
	by analys estimator annyear;
run;

data bankexp; set analyst_controls_5;
	by analys estimator annyear;
	if first.estimator;
run;

proc sql;
	create table analyst_controls_6 as select unique
	a.*, b.annyear as firstbankyear
	from analyst_controls_5 as a left join bankexp as b
	on a.estimator = b.estimator
	and a.analys = b.analys;
quit;

data analyst_controls_7; set analyst_controls_6;
	bankexp = annyear - firstbankyear;
run;

**Create variable for number of firms covered by analyst (at a given bank);
proc sql;
	create table analyst_controls_8 as select *,
	count(unique ticker) as numfirmscov
	from analyst_controls_7
	group by analys, estimator, annyear;
quit;

**Create variable for broker size;
proc sql;
	create table analyst_controls_9 as select *,
	count(unique analys) as numanalystbroker
	from analyst_controls_8
	group by estimator, annyear;
quit;

**Create natural log version of variables;
data analyst_controls_10; set analyst_controls_9;
	ln_numfirmscov = log(numfirmscov);
	ln_numanalystbroker = log(numanalystbroker);
run;

/**Part 2: All-Star control variables**/
**Import All-Star data from Excel (1983-2016);
*All-star data from Lian Fen;
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;
*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;

* 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;

**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 analyst_controls_11 as select distinct
	a.*, b.star_analyst_tp1
	from analyst_controls_10 as a left join allstar_1983_2016_3 as b
	on a.analys = b.analystid
	and  a.annyear = 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 analyst_controls_12; set analyst_controls_11;
	if star_analyst_tp1=1 then allstar_dummy=1;
		else allstar_dummy=0;
	drop star_analyst_tp1;
	if annyear > 2017 then allstar_dummy = .;
run;

proc means data=analyst_controls_12;
	var allstar_dummy;
	class annyear;
run;
*11.3% of obs classified as All-Star analysts;
*NOTE: Have All-Star data from 1983-2016 (so cannot use 2017 when using All-Star as a variable - unless relying on 2016 lagged data);

**Save analyst control variable dataset to perm library;
data perm.analyst_controls; set analyst_controls_12;
run;

*Note1: firmexp, genexp, allstar_dummy all defined at analyst-year level (NOT bank); 
*Note2: bankexp, numfirmscov, and ln_numfirmscov are all bank-specific (need to merge in estimator first);
