options errorabend;
/**Create Estimid-Estimator IBES linking table**/
**Import IBES datasets;
libname ibes '../data/raw';
libname perm '../data';

*1) IBES Recommendation Detail;
data recddet_1; set ibes.recddet;
	where (usfirm=1) /*and (year(anndats) ge 1998)*/ and (ticker ne " ");
	anndats_year = year(anndats);
	keep ticker estimid amaskcd anndats_year;
run;

*2) 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 " ");
	anndats_year = year(anndats);
	keep ticker estimator analys anndats_year;
run;

**Create potential estimdid-estimator merges based on firm-analyst-year pairs;
proc sql;
	create table estimid_estimator_merge_1 as select
	a.*, b.*
	from recddet_1 as a left join det_epsus_1 as b
	on a.ticker = b.ticker
	and a.amaskcd = b.analys
	and a.anndats_year = b.anndats_year;
quit;

*Keep estimid and estimator variables;
data estimid_estimator_merge_2; set estimid_estimator_merge_1;
	keep estimid estimator;
run;

*Sort by estimid-estimator pairs;
proc sort data=estimid_estimator_merge_2;
	by estimid estimator;
run;

*Count number of obs per estimid;
proc sql;
	create table estimid_estimator_merge_3
	as select *,
	n(estimid) as n_estimid
	from estimid_estimator_merge_2
	group by estimid;
quit;

*Count number of obs per estimid-estimator pair; 
proc sql;
	create table estimid_estimator_merge_4
	as select *,
	n(estimid) as n_estimid_estimator
	from estimid_estimator_merge_3
	group by estimid, estimator;
quit;

**Keep one obs per potential estimator-estimid pair;
proc sort data=estimid_estimator_merge_4 out=estimid_estimator_merge_5 nodupkey;
	by estimid descending n_estimid_estimator n_estimid estimator;
run;

*Calculate estimid-estimator count as percent of total estimid count; 
data estimid_estimator_merge_6; set estimid_estimator_merge_5;
	percent_estimator = (n_estimid_estimator / n_estimid);
run;

**Keep one obs per estimid-estimator pair (e.g., best match);
proc sort data=estimid_estimator_merge_6 out=estimid_estimator_merge_7 nodupkey;
	by estimid;
run;

*Sort by number of estimid observations (descending);
proc sort data=estimid_estimator_merge_7;
	by descending n_estimid;
run;
*Note: Match (percent_estimator) seems quite good, especially for large brokers (>90%);

**Save estimid-estimator merge dataset to perm library;
data perm.estimid_estimator_merge; set estimid_estimator_merge_7;
run;



