options errorabend;
/* This needs to run on SAS 9.4 (Unicode Support)*/
libname ibes '../data/raw';
libname perm '../data';
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
/***IBES Data Cleaning Recommendation Dataset***/
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
/**Step 1: Pull IBES data**/
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
***Import various IBES datasets;
**1) IBES Price Target Detail;
data ptgdet_1; 
	set ibes.ptgdet ;
	where (estcur="USD") and (curr="USD") and usfirm=1
		  and (year(anndats) ge 1999) and horizon="12" and measure="PTG"
		  and (ticker ne " ") and (value ne .) and measure="PTG" 
		  and horizon="12";
run;
*NOTE: Earliest price target obs is 2/19/1999;
	*Seems to be better populated starting in March 1999;

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

**3) 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 " ");
run;
*Note: Requiring non-missing actuals drops long-term growth forecasts (fpi="0");


*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
/**Step 2: Perform data cleaning for Target Price dataset**/
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*Keep relevant variables and create datetime variable;
data ptgdet_2; set ptgdet_1
	(drop = cusip oftic actdats horizon estcur curr usfirm measure acttims);
	datetime = dhms(anndats,0,0,anntims);
	format datetime datetime.;
	annyear = year(anndats);
run;

*Drop obs with amaskcd = 000000 (missing analyst name);
data ptgdet_3; set ptgdet_2;
	where amaskcd ne 0;
run;

*Sort by analyst, firm, and TP forecast date;
proc sort data=ptgdet_3;
	by amaskcd estimid ticker datetime;
run;

*Create counter variable of # TP obs per analyst-firm pair;
data ptgdet_4; set ptgdet_3;
	by amaskcd estimid ticker datetime;
	retain count;
	if first.ticker then do;
		count=1;
		end;
	output;
	count=count+1;
run;

*Add count to datetime var to avoid duplicates for proc expand;
data ptgdet_5; set ptgdet_4;
	datetime_1 = datetime + count;
	format datetime_1 datetime.;
run;

*Sort and check for duplicates;
proc sort data=ptgdet_5 out=ptgdet_6 nodupkey;
	by amaskcd estimid ticker datetime_1;
run;
*No duplicates;
**Get lagged TP variables;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=ptgdet_6 out=ptgdet_7 method=none;
	convert value = value_tm1 / transform=(lag 1);
	convert anndats = anndats_tm1 / transform=(lag 1);
	by amaskcd estimid ticker;
	id datetime_1;
run;
proc printto; run;

proc sort data=ptgdet_7;
	by amaskcd estimid ticker datetime_1;
run;

**Create reiteration dummy;
data ptgdet_8; set ptgdet_7;
	if (value = value_tm1) and (value_tm1 ne .) then reiteration=1;
		else if value ne value_tm1 and (value_tm1 ne .) then reiteration=0;
			else reiteration=.;
run;

**Drop reiteration obs (doing so will be important to get correct days btwn TP);
data ptgdet_9; set ptgdet_8;
	where reiteration ne 1;
run;

*Drop variables before re-running proc expand lag command;
data ptgdet_9; set ptgdet_9
	(drop = value_tm1 anndats_tm1 reiteration);
run;
**Re-run proc expand to get lagged TP variables (after dropping reiteration obs from sample);
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=ptgdet_9 out=ptgdet_10 method=none;
	convert value = value_tm1 / transform=(lag 1);
	convert anndats = anndats_tm1 / transform=(lag 1);
	by amaskcd estimid ticker;
	id datetime_1;
run;
proc printto; run;

proc sort data=ptgdet_10;
	by amaskcd estimid ticker datetime_1;
run;

**Drop obs with missing lagged value (cannot calculate percent change for these obs);
data ptgdet_11; set ptgdet_10;
	where value_tm1 ne .;
run;

*Also drop obs with lagged value equal to zero (also cannot calculate % chg for these obs);
data ptgdet_12; set ptgdet_11;
	where value_tm1 ne 0;
run; 

**Create percent change and days between revision variables;
data ptgdet_13; set ptgdet_12;
	pctchg_tp = (value - value_tm1)/(value_tm1);
	abs_pctchg_tp = abs(pctchg_tp);
	days_btwn_tp = (anndats - anndats_tm1);
run;

*Check descriptives (pre-trimming);
proc means data=ptgdet_13 n mean min p1 p5 p10 p25 p50 p75 p90 p95 p99 max;
	var pctchg_tp abs_pctchg_tp days_btwn_tp;
run;
*NOTE: should be sufficient to winsorize/trim these variables at the 1/99% level;

**Save full TP revision dataset to perm library;
data perm.tp_revisions; set ptgdet_13;
run;
*NOTE1: Can use this dataset for merging in TP revisions to other datasets;
*NOTE2: This dataset has not winsorized pctchg_tp, abs_pctchg_tp, and days_btwn_tp;
*NOTE3: Create an upgrade/downgrade dummy for TP revisions?;

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
/**Step 3: Perform data cleaning for Recommendation dataset**/
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*Keep relevant variables and create datetime variable;
data recddet_2; set recddet_1
	(drop = cusip oftic actdats usfirm acttims revtims
			ereccd etext);
	datetime = dhms(anndats,0,0,anntims);
	format datetime datetime.;
	annyear = year(anndats);
	ireccd_1 = input(ireccd, 8.); *Converting ireccd to numeric value;
	drop ireccd;
	rename ireccd_1 = ireccd;
run;

*Drop obs with amaskcd = 000000 (missing analyst name);
data recddet_4; set recddet_2;
	where amaskcd ne 0;
run;

*Sort by analyst, firm, and Rec date;
proc sort data=recddet_4;
	by amaskcd estimid ticker datetime;
run;

*Create counter variable of # Rec obs per analyst-firm pair;
data recddet_5; set recddet_4;
	by amaskcd estimid ticker datetime;
	retain count;
	if first.ticker then do;
		count=1;
		end;
	output;
	count=count+1;
run;

*Add count to datetime var to avoid duplicates for proc expand;
data recddet_6; set recddet_5;
	datetime_1 = datetime + count;
	format datetime_1 datetime.;
run;

*Sort and check for duplicates;
proc sort data=recddet_6 out=recddet_7 nodupkey;
	by amaskcd estimid ticker datetime_1;
run;
*No duplicates;
**Get lagged Rec variables;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=recddet_7 out=recddet_8 method=none;
	convert ireccd = ireccd_tm1 / transform=(lag 1);
	convert anndats = anndats_tm1 / transform=(lag 1);
	by amaskcd estimid ticker;
	id datetime_1;
run;
proc printto; run;

proc sort data=recddet_8;
	by amaskcd estimid ticker datetime_1;
run;

**Create initiation and reiteration dummies;
data recddet_9; set recddet_8;
	if ireccd_tm1 = . then initiation = 1;
		else initiation = 0;
	if ireccd = ireccd_tm1 then reiteration = 1;
		else reiteration = 0;
run;

proc means data=recddet_9;
	var initiation reiteration;
run;

**Drop reiteration obs (doing so will be important to get correct days btwn Rec);
data recddet_10; set recddet_9;
	where reiteration ne 1;
run;

data reiterations; set recddet_9;
	where reiteration = 1;
	drop ireccd_tm1 anndats_tm1;
	rec5_lvl = (ireccd * -1) + 6;
run;

*Drop variables before re-running proc expand lag command;
data recddet_11; set recddet_10
	(drop = ireccd_tm1 anndats_tm1);
run;
**Re-run proc expand to get lagged Rec variables (after dropping reiteration obs from sample);
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=recddet_11 out=recddet_12 method=none;
	convert ireccd = ireccd_tm1 / transform=(lag 1);
	convert anndats = anndats_tm1 / transform=(lag 1);
	by amaskcd estimid ticker;
	id datetime_1;
run;
proc printto; run;

proc sort data=recddet_12;
	by amaskcd estimid ticker datetime_1;
run;

proc means data=recddet_12;
	var initiation;
run;
*NOTE: Should initiations be able to merge with any TP revision obs? Would not be a true initiation in that case;

**Create Rec change and days between revision variables;
*Note: Using five-point scale;
data recddet_13; set recddet_12;
	days_btwn_rec = (anndats - anndats_tm1);
	rec5_chg = ((ireccd - ireccd_tm1) * -1);
	rec5_lvl = (ireccd * -1) + 6;
	rec5_lvl_tm1 = (ireccd_tm1 * -1) + 6;
run;
*Note1: for ireccd, 1=Strong Buy, 2=Buy, 3=Hold, 4=Underperform, 5=Sell;
*Note2: Multiplying changes by negative 1 so UPGRADES=POSITIVE and DOWNGRADES=NEGATIVE;
*Note3: *Multiplying levels by negative 1, then adding 6 so 5=Strong Buy, 4=Buy, 3=Hold, 2=Underperform, 1=Sell;

**Create indicators for upgrades and downgrades(using five-point scale);
data recddet_14; set recddet_13;
	if initiation=1 then upgrade_5=.;
		else if rec5_chg gt 0 then upgrade_5=1;
			else if rec5_chg lt 0 then upgrade_5=0;
	if initiation=1 then downgrade_5=.;
		else if rec5_chg lt 0 then downgrade_5=1;
			else if rec5_chg gt 0 then downgrade_5=0;
run; 

*Check descriptives (pre-trimming);
proc means data=recddet_14 n mean min p1 p5 p10 p25 p50 p75 p90 p95 p99 max;
	var days_btwn_rec rec5_chg rec5_lvl rec5_lvl_tm1 
		initiation upgrade_5 downgrade_5;
run;
*Think about how to handle extreme values for days_btwn_rec;

data recddet_15; set recddet_14 reiterations;
run;

proc sort data=recddet_15;
	by amaskcd estimid ticker datetime_1;
run;

**Save full Rec initiation/revision dataset to perm library;

* Includes reiterations;
data perm.rec_obs; set recddet_15;
run;
*NOTE1: Can use this dataset for merging in Rec revisions/initiations to other datasets;
*NOTE2: This dataset has not winsorized days_btwn_rec;



*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
/**Step 4: Perform data cleaning for EPS forecast dataset**/
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*Keep relevant variables and create datetime variable;
data det_epsus_2; set det_epsus_1
	(drop = cusip oftic actdats currfl curr usfirm
			acttims revdats revtims actdats_act acttims_act
			curr_act report_curr);
	datetime = dhms(anndats,0,0,anntims);
	format datetime datetime.;
	annyear = year(anndats);
/*	where year(anndats) lt 2018;*/
run;

**Keep one-year ahead ANNUAL EPS forecasts;
data det_epsus_3; set det_epsus_2;
	where fpi="1" or fpi="2";
run;
*Note1: One-qtr ahead is fpi="6" (two-qtr ahead is fpi="7");
*Note2: LTG forecast is fpi="0";
			
*Drop obs with amaskcd = 000000 (missing analyst name);
data det_epsus_4; set det_epsus_3;
	where analys ne 0;
run;

*Sort by analyst, firm, and EPS forecast date;
proc sort data=det_epsus_4;
	by analys estimator ticker fpedats datetime;
run;

*Drop duplicates;
proc sort data=det_epsus_4 out=det_epsus_5 nodupkey;
	by analys estimator ticker fpedats datetime value /*actual*/;
run;

*Create counter variable of # EPS obs per analyst-firm pair;
data det_epsus_6; set det_epsus_5;
	by analys estimator ticker fpedats datetime;
	retain count;
	if first.fpedats then do;
		count=1;
		end;
	output;
	count=count+1;
run;

*Add count to datetime var to avoid duplicates for proc expand;
data det_epsus_7; set det_epsus_6;
	datetime_1 = datetime + count;
	format datetime_1 datetime.;
run;
**Get lagged EPS forecast variables;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=det_epsus_7 out=det_epsus_8 method=none;
	convert value = value_tm1 / transform=(lag 1);
	convert anndats = anndats_tm1 / transform=(lag 1);
	by analys estimator ticker fpedats;
	id datetime_1;
run;
proc printto; run;
proc sort data=det_epsus_8;
	by analys estimator ticker fpedats datetime_1;
run;

**Drop reiteration obs;
data det_epsus_9; set det_epsus_8;
	where value ne value_tm1;
run;

*Drop variables before re-running proc expand lag command;
data det_epsus_10; set det_epsus_9
	(drop = value_tm1 anndats_tm1);
run;

**Re-run proc expand to get lagged EPS forecast variables (after dropping reiteration obs from sample);
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=det_epsus_10 out=det_epsus_11 method=none;
	convert value = value_tm1 / transform=(lag 1);
	convert anndats = anndats_tm1 / transform=(lag 1);
	by analys estimator ticker fpedats;
	id datetime_1;
run;
proc printto; run;

proc sort data=det_epsus_11;
	by analys estimator ticker fpedats datetime_1;
run;

**Drop obs with missing lagged value (cannot calculate change for these obs);
data det_epsus_12; set det_epsus_11;
	where value_tm1 ne .;
run;

*Drop obs where anndats is after EA date;
data det_epsus_13; set det_epsus_12;
	where anndats lt anndats_act;
run;

**Keep one-year ahead forecasts (FPI="1");
data det_epsus_14; set det_epsus_13;
	where fpi="1";
run;

**Create change, %change, and days between revision variables;
data det_epsus_15; set det_epsus_14;
	pctchg_eps = (value - value_tm1)/(abs(value_tm1)); *Will have some missing values due to zero in denom;
	abs_pctchg_eps = abs(pctchg_eps);
	chg_eps_unscaled = (value - value_tm1); *Scale by stock price or some other variable;
	days_btwn_eps = (anndats - anndats_tm1);
run;

*Check descriptives (pre-trimming);
proc means data=det_epsus_15 n mean min p1 p5 p10 p25 p50 p75 p90 p95 p99 max;
	var pctchg_eps abs_pctchg_eps chg_eps_unscaled days_btwn_eps;
run;
*NOTE: should be sufficient to winsorize/trim these variables at the 1/99% level;

**Save full one-year ahead annual EPS forecast revision dataset to perm library;
data perm.eps_annual; set det_epsus_15;
run;
*NOTE1: Can use this dataset for merging in annual EPS revisions to other datasets;
*NOTE2: This dataset has not winsorized variables; 
*NOTE3: Create an upgrade/downgrade dummy for EPS revisions?;
*NOTE4: Create same dataset for one-QUARTER ahead EPS forecast revisions (fpi="6")?;

