/*	Last Update: March 2012			*/

This code is taken almost straight from WRDS. Here's how WRDS describes the SAS program:
Program: 	Esurprises.sas
Author:		Denys Glushkov, WRDS
Date Created:	Feb 2008
Last Modified:	Jun 2009
Location:	'/wrds/ibes/samples'
Input:		Tab-delimited text file named tickers.txt containing a column of IBES (not official) tickers stored in your home WRDS directory.
Output:		The program stores 3 types of earnings surprises (SUE1, SUE2, SUE3) and associated cumulative abnormal returns (CAR1, CAR2) in SAS dataset SUECARS in your home WRDS directory. 
		SUE1: Earnings surprise is based on a rolling seasonal random walk model (LM, page 185) 
		SUE2: Earnings surprise after exclusion of special items 
		SUE3: Earnings surprise is based on IBES reported analyst forecasts and actuals 
		CAR1: Cumulative abnormal return in (-1,1) window around earnings announcement dates 
		CAR2: Cumulative abnormal return over the period from two days after the announcement through one day after the following quarterly earnings announcement (LM, page 187).
 
 

libname ibes 'C:\Research\IBES'; 				*this folder contains one SAS dataset (list of IBES tickers I'm interested in);
libname crsp 'C:\Research\CRSP'; 				*this folder contains one SAS dataset (dsix - daily market returns file from CRSP);
libname save 'C:\Research\IBES\earnings surprise data';		*this folder contains no SAS dataset in the beginning, it will contain the final dataset in the end;

*********************************************************************************;
* Get a list of IBES tickers you're interested in.				*;
* Convert that list of IBES tickers into a text file called 'tickers.txt'	*;
*********************************************************************************;

data ticker; set ibes.link_ticker_permno;
	keep ticker;
proc export DATA= WORK.TICKER 
            OUTFILE= "C:\Research\IBES\tickers.txt" 
            DBMS=TAB REPLACE;
     PUTNAMES=YES;
run;


%let wrds = wrds.wharton.upenn.edu 4016;
options comamid=TCP;
signon wrds username=_prompt_;

***********************************;
* MAIN BODY OF THE PROGRAM        *;
***********************************;

RSUBMIT;

options errors=1 noovp;
options nocenter ps=max ls=78;
options mprint source nodate symbolgen macrogen;
options msglevel=i;

libname mine '/home/purdue/bhwang3'; *define a home directory on WRDS;

%let begindate='01jan1980'd; 	* start calendar date of fiscal period end;
%let enddate='31dec2011'd; 		* end calendar date of fiscal period end;

*variables to extract from Compustat;
%let comp_list= gvkey fyearq fqtr conm datadate rdq epsfxq epspxq prccq ajexq spiq cshoq cshprq cshfdq rdq saleq atq fyr consol indfmt datafmt popsrc datafqtr;

*variables to extract from IBES;
%let ibes_vars= ticker value fpedats anndats revdats measure fpi estimator analys pdf usfirm;

*IBES filters;
%let ibes_where1=where=(measure='EPS' and fpi in ('6','7') and &begindate<=fpedats<=&enddate);
%let ibes_where2=where=(missing(repdats)=0 and missing(anndats)=0 and 0<intck('day',anndats,repdats)<=90);

*timing and primary filters for Compustat Xpressfeed;
%let comp_where=where=(fyr>0 and (saleq>0 or atq>0) and consol='C' and popsrc='D' and indfmt='INDL' and datafmt='STD' and missing(datafqtr)=0);

*filter from LM (2006):
- earnings announcement date is reported in Compustat
- the price per share is available from Compustat as of the end of the fiscal quarter and is greater than $1
- the market (book) value of equity at the fiscal quarter end is available and is larger than $5 mil;
%let LM_filter=(missing(rdq)=0 and prccq>1 and mcap>5.0);

*define a set of auxiliary macros;
%include '/wrds/ibes/samples/cibeslink.sas';
%include '/wrds/ibes/samples/ibes_sample.sas';
%include '/wrds/comp/samples/sue.sas';
%include '/wrds/comp/samples/size_bm.sas';
%include '/wrds/ibes/samples/iclink.sas'; *build CRSP-IBES permno-ticker link;

/* -------------------------------------------------------------------- */
/* Some house cleaning                          			*/
/* -------------------------------------------------------------------- */
 
proc datasets library=work;
   delete comp_final1 comp_final2 comp_final3;
run;

proc upload infile='C:\Research\IBES\tickers.txt' outfile='/home/purdue/bhwang3/tickers.txt';
run;

*CIBESLINK macro will create a linking table CIBESLNK between IBES ticker and Compustat GVKEY
*based on IBES ticker-CRSP permno (ICLINK) and CCM CRSP permno - Compustat GVKEY (CSTLINK2) link;
%CIBESLINK (begdt=&begindate, enddt=&enddate);

*Read in IBES tickers from the specified file stored in the user's home director on WRDS;
filename input '/home/purdue/bhwang3/tickers.txt';
data tickers;
   infile input;
   informat ticker $6.;
   input @1 ticker;
run;

* Macro IBES_SAMPLE extracts the estimates from IBES Unadjusted file based on the user-provided
* input (SAS set tickers), links them to IBES actuals, puts estimates and actuals on the same basis
* by adjusting for stock splits using CRSP adjustment factor and calculates the median/mean/dispersion of analyst
* forecasts made in the 90 days prior to the earnings announcement date. Outputs file MEDEST into work directory;
%MACRO IBES_SAMPLE (infile=, ibes1_where=, ibes2_where=, ibes_var=);

proc sql; create table ibes (drop=measure fpi)
        as select *
        from ibes.detu_epsus (&ibes1_where keep=&ibes_var) as a,             
             &infile as b                                              
        where a.ticker=b.ticker
        order by a.ticker, fpedats, estimator, analys, anndats, revdats;
quit;

*Select the last estimate for a firm within broker-analyst group;     
data ibes; set ibes;
        by ticker fpedats estimator analys;
        if last.analys;
run;

*How many estimates are reported on primary/diluted basis?;
proc sql; 
        create table ibes 
                as select a.*, sum(pdf='P') as p_count, sum(pdf='D') as d_count
                from ibes as a
                group by ticker, fpedats;

* a. Link unadjusted estimates with unadjusted actuals and CRSP permnos;
* b. Adjust report and estimate dates to be CRSP trading days;
        create table ibes1 (&ibes2_where)
                as select a.*, b.anndats as repdats, b.value as act, c.permno,
                case when weekday(a.anndats)=1 then intnx('day',a.anndats,-2)                  
                     when weekday(a.anndats)=7 then intnx('day',a.anndats,-1) else a.anndats   
                end as estdats1,
                case when weekday(b.anndats)=1 then intnx('day',b.anndats,1)                  
                     when weekday(b.anndats)=7 then intnx('day',b.anndats,2) else b.anndats    
 
              end as repdats1
                from ibes as a, ibes.actu_epsus as b, mine.iclink as c
                where a.ticker=b.ticker and a.fpedats=b.pends and a.usfirm=b.usfirm and b.pdicity='QTR' 
                          and b.measure='EPS' and a.ticker=c.ticker and c.score in (0,1,2);

*   Making sure that estimates and actuals are on the same basis;
                        
*   1. retrieve CRSP cumulative adjustment factor for IBES report and estimate dates;
        create table adjfactor
                as select distinct a.*
                from crsp.dsf (keep=permno date cfacshr) as a, ibes1 as b
                where a.permno=b.permno and (a.date=b.estdats1 or a.date=b.repdats1);
        
*      2.if adjustment factors are not the same, adjust the estimate to be on the same basis with the actual;
        create table ibes1
                as select distinct a.*, b.est_factor, c.rep_factor, 
                        case when (b.est_factor ne c.rep_factor) and missing(b.est_factor)=0 and missing(c.rep_factor)=0
                         then (rep_factor/est_factor)*value else value end as new_value
                from ibes1 as a, 
                        adjfactor (rename=(cfacshr=est_factor)) as b, 
                        adjfactor (rename=(cfacshr=rep_factor)) as c 
                        where (a.permno=b.permno and a.estdats1=b.date) and
                                  (a.permno=c.permno and a.repdats1=c.date);
quit;

* Make sure the last observation per analyst is included;
proc sort data=ibes1; 
        by ticker fpedats estimator analys anndats revdats;
run;

data ibes1; set ibes1;
by ticker fpedats estimator analys;
if last.analys;
run;

* Compute the median forecast based on estimates in the 90 days prior to the report date;
proc means data=ibes1 noprint;
        by ticker fpedats;
        var new_value;                         			* new_value is the estimate appropriately adjusted;
        output out= medest (drop=_type_ _freq_)         
        median=medest n=numest std=dispersion;			* SUBJECT TO CHANGE: medest = MEDIAN or MEAN;
run;

* Merge median estimates with ancillary information on permno, actuals and report dates;
* Determine whether most analysts are reporting estimates on primary or diluted basis;
* following the methodology outlined in Livnat and Mendenhall (2006);
proc sql; create table medest 
        as select distinct a.*, b.repdats, b.act, b.permno,
        case when p_count>d_count then 'P' 
             when p_count<=d_count then 'D' 
        end as basis                                                                             
        from medest as a left join ibes1 as b
        on a.ticker=b.ticker and a.fpedats=b.fpedats;
quit;

proc sql; 
        drop table ibes, ibes1;
quit;
%MEND;

%IBES_SAMPLE (infile=tickers, ibes1_where=&ibes_where1, ibes2_where=&ibes_where2, ibes_var=&ibes_vars);

*COMPUSTAT EXTRACT;
proc sql;
   create table gvkeys
   as select a.*
   from cibeslnk as a, tickers as b
   where a.ticker=b.ticker; *use CIBESLNK table to link IBES Ticker and GVKEY;

   create table comp (drop=consol indfmt datafmt popsrc)
   as select a.*, cshoq*prccq as mcap
   from comp.fundq (keep=&comp_list &comp_where) as a,
   gvkeys as b
   where a.gvkey=b.gvkey;

   create table comp
   as select *
   from comp a left join
   (select distinct gvkey,ibtic from comp.security
   (where=(missing(ibtic)=0))) b
   on a.gvkey=b.gvkey;
quit;

*Create calendar date of fiscal period end in Compustat extract;
data comp; set comp;
   if (1<=fyr<=5) then date_fyend=intnx('month',mdy(fyr,1,fyearq+1),0,'end');
   else if (6<=fyr<=12) then date_fyend=intnx('month',mdy(fyr,1,fyearq),0,'end');
   fqenddt=intnx('month',date_fyend,-3*(4-fqtr),'end');
   format fqenddt date9.;
   drop date_fyend;
run;

* a) Link Gvkey with Lpermno;
proc sql;
   create table comp1
   as select a.*, b.lpermno
   from comp (where=(&begindate<=fqenddt<=&enddate)) as a left join lnk as b
   on a.gvkey=b.gvkey and ((b.linkdt<=a.fqenddt <=b.linkenddt) or
   (b.linkdt<=a.fqenddt and b.linkenddt=.E) or
   (b.linkdt=.B and a.fqenddt <=b.linkenddt)) and b.usedflag=1;

* b) Link Gvkey with IBES Ticker;
create table comp1
   as select a.*, b.ticker
   from comp1 as a left join cibeslnk as b
   on a.gvkey=b.gvkey and ((b.fdate<=a.fqenddt <=b.ldate) or
   (b.fdate<=a.fqenddt and b.ldate=.E) or (b.fdate=.B and a.fqenddt <=b.ldate));

* c) Link IBES analysts' expectations (MEDEST), IBES report dates (repdats)
* and actuals (act) with Compustat data;
create table comp1
   as select a.*, b.medest, b.numest, b.dispersion, b.repdats, b.act, b.basis
   from comp1 as a left join medest as b
   on a.ticker=b.ticker and
   year(a.fqenddt)*100+month(a.fqenddt)=year(b.fpedats)*100+month(b.fpedats);
quit;

*remove fully duplicate records and pre-sort;
proc sort data=comp1 noduprec; by _all_;run;
proc sort data=comp1; by gvkey fyearq fqtr;run;

* Macro SUE calculates standardized earnings surprises SUE1, SUE2, SUE3
* and outputs datasets comp_final&k into the work directory;
%MACRO Allsurprises;
   %do k=1 %to 3;
   %SUE (method=&k, input=comp1);
   %end;
%mend;
%Allsurprises;

* Merge all of the results together to get a dataset containing SUE1 , SUE2
* and SUE3 for all relevant (GVKEY-Report date) pairs;
data comp_final;
   merge comp_final1 comp_final2 (keep=gvkey fyearq fqtr sue2)
   comp_final3 (keep=gvkey fyearq fqtr sue3);
   by gvkey fyearq fqtr;
   label fqenddt='Calendar date of fiscal period end';
   keep ticker ibtic lpermno gvkey conm fyearq fqtr fyr fqenddt repdats rdq;
   keep sue1 sue2 sue3 basis actual expected deflator act medest numest dispersion prccq mcap;
run;

proc sort data=comp_final;
   *descending sort is intenational to define leads;
   by gvkey descending fyearq descending fqtr;
run;

* Shifting the announcement date to be a trading day;
* Defining the day after the following quarterly earnings announcement as leadrdq1;
data retdates; set comp_final;
   by gvkey;
   leadrdq=lag(rdq);
   if first.gvkey then leadrdq=intnx('month',rdq,3,'sameday');
   *if sunday move back by 2 days, if saturday move back by 1 day;
   if weekday(rdq)=1 then rdq1=intnx('day',rdq,-2); else
   if weekday(rdq)=7 then rdq1=intnx('day',rdq,-1); else rdq1=rdq;
   if weekday(leadrdq)=1 then leadrdq1=intnx('day',leadrdq,2); else
   if weekday(leadrdq)=7 then leadrdq1=intnx('day',leadrdq,3); else
   if weekday(leadrdq)=6 then leadrdq1=intnx('day',leadrdq,3); else
   leadrdq1=intnx('day',leadrdq,1);
   if leadrdq=rdq then delete;
   keep lpermno gvkey fyearq fqtr rdq1 leadrdq1 rdq;
   format rdq1 leadrdq1 date9.;
run;

* Apply LM filter
* Earnings report dates in Compustat and in IBES (if available)
* should not differ by more than one calendar day;
data comp_final;
   set comp_final;
   if &LM_filter and (((missing(sue1)=0 or missing(sue2)=0) and missing(repdats)=1)
   or (missing(repdats)=0 and abs(intck('day',repdats,rdq))<=1));
run;

* Extract file of raw daily returns around between earnings announcement dates;
proc sql;
   create table crsprets
   as select a.*, b.rdq1, b.leadrdq1, b.rdq
   from crsp.dsf (keep=permno ret date where=(&begindate<=date<=&enddate)) as a,
   retdates (where=(missing(rdq1)=0 and missing(leadrdq1)=0 and
   30<intck('day',rdq1,leadrdq1))) as b
   where a.permno=b.lpermno and intnx('day',rdq1,-60)<=a.date<=intnx('day',leadrdq1,5);
quit;



* Macro SIZE_BM assigns the stocks into six Size-BM portfolios based on the methodology
* outlined on Ken French webiste at
* http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library/six_portfolios.html
* The size breakpoint for year t is the median NYSE market equity at the end of June of year t.
* BE/ME for June of year t is the book equity for the last fiscal year end in t-1 divided by ME
* for December of t-1. The BE/ME breakpoints are the 30th and 70th NYSE percentiles.
* Link dataset LINK is a linking table between GVKEY and NPERMNO with valid link dates
* After execution macro SIZE_BM creates dataset SIZE_BM_PORT containing size/BM stock assignments;
%SIZE_BM (bdate=&begindate, edate=&enddate, link=lnk);


proc download data=comp_final out=comp_final;			*meanest - if consensus forecast based on mean;
proc download data=crsprets out=crsprets;
proc download data=size_bm_port out=size_bm_port;
proc download data=ff.portfolios_d out=portfolios_d;
run;

ENDRSUBMIT;


*****************************************************************************************************************************************;
* Save datasets on local computer.													*;
* I actually ran this code twice (I know it's not very efficient):  									*;
* Once for consensus forecast = MEDIAN forecast --> comp_final, once for consensus forecast = MEAN forecast --> comp_final_meanest;	*;
*****************************************************************************************************************************************;

data save.comp_final; set comp_final; 				*meanest - if consensus forecast based on mean;
data save.crsprets; set crsprets;
data save.size_bm_port; set size_bm_port;
data save.portfolios_d; set portfolios_d;
run;


data a; set save.comp_final;
	if lpermno ne .;
	abs_sue1 = abs(sue1);
proc sort data=a; by lpermno rdq fqenddt descending abs_sue1;	* there's very few cases with "duplicates";
proc sort data=a nodupkey; by lpermno rdq fqenddt;		* the duplicate has the exact same information, but the sue1 (erroneously) equals zero;
run; 

data b; set save.comp_final_meanest;
	if lpermno ne .;
	abs_sue1 = abs(sue1);
proc sort data=b; by lpermno rdq fqenddt descending abs_sue1;	* there's very few cases with "duplicates";
proc sort data=b nodupkey; by lpermno rdq fqenddt;		* the duplicate has the exact same information, but the sue1 (erroneously) equals zero;
data b; set b;
	rename sue3 	= sue4;
	rename medest 	= meanest;
	keep lpermno rdq fqenddt sue3 medest dispersion; 	* whether I take dispersion from comp_final or comp_final_meanest doesnt matter;
run; 

data comp_final; merge a b; by lpermno rdq fqenddt;
	format sue1 BEST12.;
	format sue2 BEST12.;
	format sue3 BEST12.;
	format sue4 BEST12.;

   	*if sunday move forward by 1 day, if saturday move forward by 2 days;
   	if weekday(rdq)=1 then rdq1=intnx('day',rdq,+1); else
   	if weekday(rdq)=7 then rdq1=intnx('day',rdq,+2); else rdq1=rdq;
   	format rdq1 YYMMDDN8.;
	drop abs_sue1;
run;

data crsprets; set save.crsprets;
	drop rdq1 leadrdq1;
data size_bm_port; set save.size_bm_port;
data portfolios_d; set save.portfolios_d;
	keep date smlo_ewret smme_ewret smhi_ewret bilo_ewret bime_ewret bihi_ewret;
run;

data index; set crsp.dsix;
	rename CALDT = date;
	n+1;
	keep CALDT n;
run;

data crsprets1; set crsprets;
	if month(rdq) > 6 then ryear = year(rdq); else ryear = year(rdq)-1;

   *if sunday move forward by 1 day, if saturday move forward by 2 days;
   if weekday(rdq)=1 then rdq1=intnx('day',rdq,+1); else
   if weekday(rdq)=7 then rdq1=intnx('day',rdq,+2); else rdq1=rdq;
   format rdq1 YYMMDDN8.;
run;

*assign bm and size;
proc sort data=crsprets1; by permno rdq date;
data size_bm_port1; set size_bm_port;
	ryear = year(size_date);
	keep permno ryear size_port bm_port;
proc sort data=size_bm_port1; by permno ryear;
data crsprets1; merge crsprets1 (in=m1) size_bm_port1; by permno ryear;
	if m1;
run;

*get abnormal returns;
proc sort data=crsprets1; by date;
proc sort data=portfolios_d; by date;
data crsprets1; merge crsprets1 (in=m1) portfolios_d index; by date;
	if m1;

   	if size_port='Small' and bm_port='Low' 		then abret=ret-smlo_ewret;
   	if size_port='Small' and bm_port='Medium' 	then abret=ret-smme_ewret;
   	if size_port='Small' and bm_port='High' 	then abret=ret-smhi_ewret;
   	if size_port='Big' and bm_port='Low' 		then abret=ret-bilo_ewret;
   	if size_port='Big' and bm_port='Medium' 	then abret=ret-bime_ewret;
	if size_port='Big' and bm_port='High' 		then abret=ret-bihi_ewret;

	if intck('days', date, rdq1) = 0 then i=n;

	keep permno date ret abret rdq1 rdq i n;
data index; set crsprets1;
	if intck('days', date, rdq1) = 0;
	keep permno rdq rdq1 i;
data crsprets1; set crsprets1;
	drop i;
run;

*get event-time count based on RDQ1 <-- !!! (not RDQ);
proc sort data=crsprets1; by permno rdq rdq1;
proc sort data=index; by permno rdq rdq1;
data crsprets1; merge crsprets1 index; by permno rdq rdq1;
	a = n-i;
	drop n i;
run;



proc means data=crsprets1 noprint;
   where 0 <= a <= 0;
   by permno rdq rdq1;
   var abret ret;
   output out=CAR0 (rename=(_freq_=daysCAR0)) sum=CAR0 R0;
proc means data=crsprets1 noprint;
   where 1 <= a <= 1;
   by permno rdq rdq1;
   var abret ret;
   output out=CAR1 (rename=(_freq_=daysCAR1)) sum=CAR1 R1;
proc means data=crsprets1 noprint;
   where 1 <= a <= 40;
   by permno rdq rdq1;
   var abret ret;
   output out=CAR2 (rename=(_freq_=daysCAR2)) sum=CAR2 R2;
proc means data=crsprets1 noprint;
   where 2 <= a <= 41;
   by permno rdq rdq1;
   var abret ret;
   output out=CAR3 (rename=(_freq_=daysCAR3)) sum=CAR3 R3;
proc means data=crsprets1 noprint;
   where 1 <= a <= 60;
   by permno rdq rdq1;
   var abret ret;
   output out=CAR4 (rename=(_freq_=daysCAR4)) sum=CAR4 R4;
proc means data=crsprets1 noprint;
   where 2 <= a <= 61;
   by permno rdq rdq1;
   var abret ret;
   output out=CAR5 (rename=(_freq_=daysCAR5)) sum=CAR5 R5;
run;

data car; merge car0 (in=m1) car1 (in=m2) car2 car3 car4 car5; by permno rdq rdq1;
	if m1 and m2;
run;


* Merge surprises with abnormal returns and place the final set into home directory;
* Use daysCAR1 and daysCAR2 to check for whether CAR1 and CAR2 are computed over
* potentially misleading time windows due to missing returns data in CRSP DSF file;
proc sql;
   create table suecars
   as select a.gvkey, a.lpermno label='CRSP PERMNO Identifier',
   a.ticker label='Historical IBES Ticker', a.ibtic, a.rdq, a.rdq1 label='Report Date of Quarterly Earnings (Trading Day)', a.fqenddt, a.fyearq, a.fqtr, 
   a.sue1   label='Earnings Surprise (Seasonal Random Walk)',
   a.sue2   label='Earnings Surprise (Excluding Special items)',
   a.sue3   label='Earnings Surprise (Analyst Forecast-based: MEDIAN)',
   a.sue4   label='Earnings Surprise (Analyst Forecast-based: MEAN)',
   a.numest label='Number of analyst forecasts used in Analyst-based SUE',
   a.dispersion label='Standard Deviation in Analyst Forecasts',
   a.deflator,

   b.car0 		label='AR (0,0)',
   b.r0   		label='Return (0,0)',
   b.daysCAR0 	label='Actual days used in CAR0 calculation',
   b.car1 		label='AR (1,1)',
   b.r1   		label='Return (1,1)',
   b.daysCAR1 	label='Actual days used in CAR1 calculation',
   b.car2 		label='AR (1,39)',
   b.r2   		label='Return (1,39)',
   b.daysCAR2 	label='Actual days used in CAR2 calculation',
   b.car3 		label='AR (2,40)',
   b.r3   		label='Return (2,40)',
   b.daysCAR3 	label='Actual days used in CAR3 calculation',
   b.car4 		label='AR (1,59)',
   b.r4   		label='Return (1,59)',
   b.daysCAR4 	label='Actual days used in CAR4 calculation',
   b.car5 		label='AR (2,60)',
   b.r5   		label='Return (2,60)',
   b.daysCAR5 	label='Actual days used in CAR5 calculation'

   from comp_final as a, car as b
   where a.lpermno=b.permno and a.rdq=b.rdq;
quit;

proc sort data=suecars out=save.suecars_20120212; by gvkey fyearq fqtr;
run;







%let wrds = wrds.wharton.upenn.edu 4016;
options comamid=TCP;
signon wrds username=_prompt_;

rsubmit;

data actu; set ibes.actu_epsus;
	if ticker ne '';
	rename PENDS = fqenddt;
	keep ticker ANNDATS ANNTIMS PENDS;
proc download data=actu out=save.ea_date_time_20120212;
run;

endrsubmit;






libname save 	'C:\Research\IBES\earnings surprise data';		*this folder contains the final dataset from the previous lines of code;
libname m	'C:\Research\Earnings_Market';				*this folder contains no SAS dataset in the beginning, it will contain the final dataset in the end;
libname c 	'C:\Research\CRSP'; 					*this folder contains one SAS dataset (dsix - daily market returns file from CRSP);


*get quarterly earnings data (in particular the earnings announcement time);
proc sort data=save.ea_date_time_20120212 out=ibes nodupkey; by ticker fqenddt;
run;

*merge with 'suecars';
data sue; set save.suecars_20120212;
	if ticker ne '';
proc sort data=sue; by ticker fqenddt;
run;

data ibes; merge ibes sue (in=m1); by ticker fqenddt;
	if m1;
	rename lpermno = permno;
	label anndats = 'Report Date of Quarterly Earnings in IBES';
	label anntims = 'Report Time of Quarterly Earnings in IBES';
	diff_repdats = intck('day',rdq,anndats);
	date = rdq1; format date YYMMDDN8.;				*<--- IMPORTANT (if earnings are announced on weekend, rdq1 is the date of the following Monday);
run;

*save;
proc sort data=ibes out=m.ibes; by date;
run;





*get market return data;
data ret1; set c.dsix; by caldt;
	rename caldt 	= date;
	lagvwretd 		= lag(vwretd);
	lagdate			= lag(caldt);
proc sort data=ret1; by descending date;
data ret1; set ret1; by descending date;
	leadvwretd 	= lag(vwretd);
	leaddate	= lag(date);
	format date YYMMDDN8.;
	format lagdate  YYMMDDN8.;
	format leaddate YYMMDDN8.;
	keep date lagdate leaddate vwretd lagvwretd leadvwretd;
proc sort data=ret1; by date;
run;


****************;
* MAIN RESULT	;
****************;

data final; merge m.ibes (in=m1) ret1; by date;
	if m1;

	if sue1 > 0 then es1_pos=1;  				*earnings surprise based on seasonal random walk;
	else if sue1 = 0 then es1_pos=1;
	else es1_pos=-1; 
	if sue1=. then es1_pos=.;

	if sue3 > 0 then es3_pos=1;  				*earnings surprise based on MEDIAN analyst forecast;
	else if sue3 = 0 then es3_pos=1;
	else es3_pos=-1; 
	if sue3=. then es3_pos=.;

	if sue4 > 0 then es4_pos=1;  				*earnings surprise based on MEAN analyst forecast;
	else if sue4 = 0 then es4_pos=1;
	else es4_pos=-1; 
	if sue4=. then es4_pos=.;

	if -1000 < sue1 < 1000;					*remove outliers;
	if -1000 < sue4 < 1000;					*remove outliers;

	*if earnings are announced after 4pm ON A WEEKDAY then take returns from next trading day;
	*weekends are already taken care of in the suecars program (hence the difference between rdq and rdq1;
	if hour(ANNTIMS) = 0  then missing_time=1; 
	else if ANNTIMS  = .  then missing_time=1;
	else missing_time=0;

	if hour(ANNTIMS) >= 16 then after=1; else after=0;
    	if weekday(rdq)=1 then after=0;
    	if weekday(rdq)=7 then after=0;
	if missing_time = 1 then after = .;

	if after = 1 then market = leadvwretd;
	if after = 0 then market = vwretd; 	

	if after = 1 then rdq2 = leaddate;
	if after = 0 then rdq2 = date; 	

	if after = 1 then firm_car = CAR1;
	if after = 0 then firm_car = CAR0;
	if after = 1 then firm_ret = r1;
	if after = 0 then firm_ret = r0;
	if after = 1 then firm_car_long = CAR5;
	if after = 0 then firm_car_long = CAR4;

	*clean up;
	if diff_repdats = 0;					*diff_repdats is the difference between the report date in IBES and the report date in Compustat - given the importance of getting the timing right, I remove observations where there are discrepancies;
	if market ne .; 					*this will kick out all observations with missing time b/c 'if missing_time = 1 then after = .';
	if sue1 ne .;
	if sue4 ne .;
	if firm_ret ne .;
	if firm_car ne .;
	if firm_car_long ne .;
proc rank data=final groups=4 out=final;
	var market vwretd;
	ranks marketr vwretdr;
data final; set final;
	marketr  	= marketr+1;

	*get main variables into percentage form;
	firm_ret 	= firm_ret * 100;
	firm_car 	= firm_car * 100;
	firm_car_long 	= firm_car_long * 100;
run;

proc sort data=final; by descending es4_pos marketr;
proc means data=final n mean maxdec=3;
	var firm_ret firm_car firm_car_long sue1 sue4;
	by descending es4_pos marketr;
run;