*****************************************************************************************************************************************************************************************************************; * Last Update: August 2016 *; * *; * This SAS code generates a SAS dataset with earnings surprises. *; * *; * This code borrows heavily from the Esurprises.sas program posted on WRDS. *; *****************************************************************************************************************************************************************************************************************; libname ibes 'C:\...\data\ibes'; *this folder contains one SAS dataset (list of IBES tickers I'm interested in); libname crsp 'C:\...\data\crsp'; *this folder contains one SAS dataset (dsf file from CRSP); libname save 'C:\...\data\ibes\tmp'; *this folder contains no SAS dataset in the beginning, it will contain some temporary datasets 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= "G:\Dropbox\data\ibes\tmp\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/cornell/username'; *define a home directory on WRDS - PUT IN YOUR OWN USERNAME WHERE IT SAYS "username"; %let begindate='01jan1980'd; *start calendar date of fiscal period end; %let enddate='31dec2016'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 00 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/ibes/samples/iclink.sas'; *build CRSP-IBES permno-ticker link; proc datasets library=work; delete comp_final1 comp_final2 comp_final3; run; proc upload infile='G:\Dropbox\data\ibes\tmp\tickers.txt' outfile='/home/cornell/username/tickers.txt'; *PUT IN YOUR OWN USERNAME WHERE IT SAYS "username"; 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/cornell/username/tickers.txt'; *PUT IN YOUR OWN USERNAME WHERE IT SAYS "username"; 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)); * 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 (drop=deflator) comp_final2 (keep=gvkey fyearq fqtr sue2) comp_final3 (keep=gvkey fyearq fqtr sue3 deflator); 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; by gvkey descending fyearq descending fqtr; 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; proc download data=comp_final out=comp_final; *meanest - if consensus forecast based on mean; run; proc datasets library=work; delete comp_final1 comp_final2 comp_final3; 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/cornell/username/tickers.txt'; *PUT IN YOUR OWN USERNAME WHERE IT SAYS "username"; 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_) mean=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)); * 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 (drop=deflator) comp_final2 (keep=gvkey fyearq fqtr sue2) comp_final3 (keep=gvkey fyearq fqtr sue3 deflator); 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; by gvkey descending fyearq descending fqtr; 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; proc download data=comp_final out=comp_final_meanest; *meanest - if consensus forecast based on mean; run; ENDRSUBMIT; *****************************************************************************************************************************************; * Save datasets on local computer. *; * As you can see above, I actually run 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; run; data save.comp_final_meanest; set comp_final_meanest; run; *****************************************************************************************************************************************; * Get time of earnings announcement. *; * Sometimes, the date/time is something like '01APR1989 0:00:00'. *; * For these cases, I assume earnings were announced sometime during 01APR1989. *; *****************************************************************************************************************************************; %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=ea_date_time; run; endrsubmit; proc sort data=ea_date_time out=save.ea_date_time nodupkey; by ticker fqenddt anndats; run; *****************************************************************************************************************************************; * Merge earnings file. *; *****************************************************************************************************************************************; 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.; run; proc sort data=comp_final nodupkey; by ticker fqenddt; data comp_final; merge comp_final (in=m1) save.ea_date_time; by ticker fqenddt; if m1; run; data comp_final; set comp_final; if anndats = . then anndats = rdq; *if sunday move forward by 1 day, if saturday move forward by 2 days; if weekday(ANNDATS)=1 then rdq1=intnx('day',ANNDATS,+1); else if weekday(ANNDATS)=7 then rdq1=intnx('day',ANNDATS,+2); *if earnings are announced after 4pm ON A WEEKDAY then take returns from next trading day; *weekends are already taken care of; else if 2 <= weekday(ANNDATS) <= 6 and hour(ANNTIMS) >= 16 then rdq1=intnx('day',ANNDATS,+1); else rdq1=ANNDATS; format rdq1 YYMMDDN8.; if hour(ANNTIMS) = 0 then missing_time=1; else if ANNTIMS = . then missing_time=1; else missing_time=0; run; *****************************************************************************************************************************************; * Merge in only the relevant returns. *; *****************************************************************************************************************************************; data tmp; set comp_final; keep lpermno rdq1; proc sort data=tmp nodupkey; by lpermno rdq1; run; proc sql; create table crsprets as select a.*, b.permno, b.ret, b.date from tmp as a, crsp.dsf as b where a.lpermno=b.permno and intnx('day',a.rdq1,0)<=b.date<=intnx('day',a.rdq1,5); quit; proc sort data=crsprets; by permno rdq1 date; data crsprets_a; set crsprets; by permno rdq1 date; i+1; if rdq1 ne lag(rdq1) then i=1; if first.permno then i=1; if i <= 3; proc sort data=crsprets_a nodupkey out=crsprets_b; by permno rdq1; data crsprets_b; set crsprets_b; rename date = rdq_t; label date = 'First Trading Day'; rename ret = ret0; keep permno rdq1 date ret; proc means data=crsprets_a noprint; var ret; by permno rdq1; output out=crsprets_a (drop = _TYPE_ _FREQ_) sum=cret03 n=obs; data crsprets; merge crsprets_a crsprets_b; by permno rdq1; if obs = 3; drop obs; label ret0 = 'Return [0]'; label cret03 = 'Cumulative Return [0,3]'; run; *****************************************************************************************************************************************; * Merge surprises with abnormal returns and place the final set into home directory; *****************************************************************************************************************************************; 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.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.rdq_t, b.ret0, b.cret03 from comp_final as a, crsprets as b where a.lpermno=b.permno and a.rdq1=b.rdq1; quit; proc sort data=suecars out=ibes.suecars; by gvkey fyearq fqtr; run;