SAS macro to get analysts EPS consensus for a given fiscal period end (DATADATE) by a selected date (DATE)

Update: contains error! will update later.

I write this macro to compute analysts’ quarterly EPS consensus on a selected date (DATE) for a given fiscal quarter end (DATADATE). This macro can be easily modified for other types of estimates (e.g., annual EPS).

This macro currently extracts unadjusted quarterly EPS estimates (current or next quarter) issued within a specified window (e.g., WINDOW = 60 days) before the selected date (DATE), and computes the consensus at median and mean. This macro also extracts unadjusted actual EPS and put estimate and actual on the same per share basis. Please see Overview of Thomson-Reuters IBES for why this is the preferred method.

The output includes the mean and median of analysts’ EPS forecasts, the number of analysts forecasts, and the actual EPS. Please note the actual EPS is “street” EPS from I/B/E/S and may be different from reported EPS from Compustat (see I/B/E/S FAQ). With these outputs, you are able to compute earnings surprises easily.

This macro is modified from Post-Earnings Announcement Drift program on WRDS. I thank the author, Denys Glushkov, who is my favorite SAS programmer.

I looked into I/B/E/S database more closely and noted a few facts:

Since 1998 only 1.3% of analysts’ forecasts are reported on the primary basis (v.s. 74.4% before 1998), making the variable basis not important any more;
During the period from the issuance date of an individual quarterly EPS forecast to the announcement date of the actual EPS, stock split or reverse stock split happens in only 0.6% of observations. Thus, putting estimate and actual on the same per share basis is not important if you specify a short window, e.g., 60 days.
anndats in I/B/E/S Actuals Detail refers to the earnings announcement data. It is supposed to be the same with rdq in Compustat Quarterly. However, this is not true in 20% of observations. WRDS suggests that rdq is more accurate (See here).

 %MACRO CONSENSUS(INFILE=, DATE=, DATADATE=, WINDOW=, OUTFILE=);

/* This macro is used to compute analysts' quarterly EPS consensus on a selected date  */
/* for a given fiscal quarter end (DATADATE). This macro can be easily modified for    */
/* other types of estimates (e.g., annual EPS).                                        */

/* This macro currently extracts unadjusted quarterly EPS estimates (current or next   */
/* quarter) issued within a specified window (e.g., WINDOW = 90 days) before the       */
/* selected date (DATE), and computes the consensus at median and mean. This macro     */
/* also extracts unadjusted actual EPS and put estimate and actual on the same per     */
/* share basis.                                                                        */

/* This macro also creates a variable "basis" to indicate whether most analysts        */
/* report estimates on primary/diluted basis. This variable is then useful when        */
/* comparing analyst consensus with Compustatís primary (EPSPXQ) or diluted (EPSFXQ)   */
/* EPS, e.g., in earnings surprise study. A noticeable fact is that since 1998 only    */
/* 1.3% of analysts' forecasts are reported on primary basis in I/B/E/S (versus 74.4%  */
/* before 1998), rendering the variable "basis" not meaningful any more. This macro    */
/* still creates the variable though.                                                  */

/* INFILE should contain IBES Ticker, PERMNO, DATE and DATADATE. OUTFILE includes the  */
/* mean and median of analysts' EPS forecasts (MEDEST and MEANEST), the number of      */
/* analysts' forecasts (NUMEST), and the actual EPS (ACT).                             */

 %local oldoptions errors;
 %let oldoptions=%sysfunc(getoption(mprint)) %sysfunc(getoption(notes))
                %sysfunc(getoption(source));
 %let errors=%sysfunc(getoption(errors));
 options notes mprint source errors=0;

/* Extract estimates from IBES Unadjusted file and keep only       */
/* the estimates issued within specified days before the selected  */
/* date "fpi in (6,7)" selects quarterly forecast for the current  */
/* and the next fiscal quarter                                     */ 
proc sql;
  create view ibes_temp
    as select a.fpedats, a.estimator, a.analys, a.anndats, a.revdats, 
        a.pdf, a.value, b.*
    from ibes.detu_epsus a, &INFILE b
    where a.ticker=b.ticker and a.fpi in ('6','7')
        and nmiss(b.&date, a.anndats)=0 and 0<=b.&date-a.anndats<=&WINDOW
        and a.fpedats=b.&datadate;

/* Count number of estimates reported on primary/diluted basis */
  create table ibes
    as select a.*, sum(pdf='P') as p_count, sum(pdf='D') as d_count
    from ibes_temp a
    group by ticker, fpedats
  order by ticker, fpedats, estimator, analys, anndats, revdats;
quit;

/* Determine whether most analysts report estimates on primary/diluted */
/* basis following Livnat and Mendenhall (2006)                        */       
data ibes; set ibes;
  by ticker fpedats estimator analys;
  if nmiss(p_count, d_count)=0 then do;
  if p_count>d_count then basis='P'; else basis='D'; end;
  if last.analys; /* Keep the latest observation for a given analyst */
run;

/* Link Unadjusted estimates with Unadjusted actuals */
proc sql;
  create table ibes1
    as select a.*, b.anndats as repdats, b.value as act 
    from ibes a left join ibes.actu_epsus b
    on a.ticker=b.ticker and a.fpedats=b.pends and b.pdicity='QTR';
    
/* select all relevant combinations of Permnos and Date */
  create table ibes_anndats
    as select distinct permno, anndats
    from ibes1
    union
    select distinct permno, repdats as anndats
    from ibes1;
    
/* Adjust all estimate and earnings announcement dates to the closest    */
/* preceding trading date in CRSP to ensure that adjustment factors wont */
/* be missing after the merge                                            */
  create view tradedates
    as select a.permno, a.anndats, b.date format=date9.
    from (select distinct permno, anndats from ibes_anndats
          where not missing(anndats)) a
    left join (select distinct date from crsp.dsi) b
    on 0<=a.anndats-b.date<=15
    group by a.permno, a.anndats
    having a.anndats-b.date=min(a.anndats-b.date);
    
/* Merge the CRSP adjustment factors for all estimate and report dates   */
    create table ibes_anndats1
    as select a.*, c.cfacshr
    from ibes_anndats a left join tradedates b
    on a.permno=b.permno and a.anndats=b.anndats
    left join crsp.dsf (keep=permno date cfacshr) c
    on a.permno=c.permno and b.date=c.date;
    
/* Put the estimate on the same per share basis as */
/* company reported EPS using CRSP Adjustment factors. New_value is the  */
/* estimate adjusted to be on the same basis with reported earnings      */
    create table ibes2
    as select a.*, (c.cfacshr/b.cfacshr)*a.value as new_value
    from ibes1 a, ibes_anndats1 b, ibes_anndats1 c
    where (a.permno=b.permno and a.anndats=b.anndats)
      and (a.permno=c.permno and a.repdats=c.anndats);
quit;
    
/* Sanity check: there should be one most recent estimate for */
/* a given firm-fiscal period end combination                 */
proc sort data=ibes2 nodupkey; by ticker fpedats estimator analys; run;

/* Compute the median forecast based on estimates in the specified days  */
/* prior to the selected date (DATE)                                     */
proc means data=ibes2 noprint;
   by ticker fpedats; id basis;
   var new_value; id repdats act permno;
   output out= consensus (drop=_type_ _freq_)        
   median=medest mean=meanest n=numest;
run;
 
/* Merge INFILE with IBES consensus */
proc sql; create table &OUTFILE
  as select distinct a.*, b.medest, b.meanest, b.numest, b.act, b.basis
  from &INFILE a left join consensus b
  on a.ticker=b.ticker and a.&datadate=b.fpedats;
quit;

proc sql;
  drop view ibes_temp, tradedates;
  drop table ibes, ibes1, ibes2, ibes_anndats, ibes_anndats1, consensus;
quit;

options errors=&errors &oldoptions;
%MEND;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

%MACRO CONSENSUS(INFILE=, DATE=, DATADATE=, WINDOW=, OUTFILE=);

/* This macro is used to compute analysts' quarterly EPS consensus on a selected date */

/* for a given fiscal quarter end (DATADATE). This macro can be easily modified for */

/* other types of estimates (e.g., annual EPS). */

/* This macro currently extracts unadjusted quarterly EPS estimates (current or next */

/* quarter) issued within a specified window (e.g., WINDOW = 90 days) before the */

/* selected date (DATE), and computes the consensus at median and mean. This macro */

/* also extracts unadjusted actual EPS and put estimate and actual on the same per */

/* share basis. */

/* This macro also creates a variable "basis" to indicate whether most analysts */

/* report estimates on primary/diluted basis. This variable is then useful when */

/* comparing analyst consensus with Compustatís primary (EPSPXQ) or diluted (EPSFXQ) */

/* EPS, e.g., in earnings surprise study. A noticeable fact is that since 1998 only */

/* 1.3% of analysts' forecasts are reported on primary basis in I/B/E/S (versus 74.4% */

/* before 1998), rendering the variable "basis" not meaningful any more. This macro */

/* still creates the variable though. */

/* INFILE should contain IBES Ticker, PERMNO, DATE and DATADATE. OUTFILE includes the */

/* mean and median of analysts' EPS forecasts (MEDEST and MEANEST), the number of */

/* analysts' forecasts (NUMEST), and the actual EPS (ACT). */

%local oldoptions errors;

%let oldoptions=%sysfunc(getoption(mprint)) %sysfunc(getoption(notes))

%sysfunc(getoption(source));

%let errors=%sysfunc(getoption(errors));

options notes mprint source errors=0;

/* Extract estimates from IBES Unadjusted file and keep only */

/* the estimates issued within specified days before the selected */

/* date "fpi in (6,7)" selects quarterly forecast for the current */

/* and the next fiscal quarter */

proc sql;

create view ibes_temp

as select a.fpedats, a.estimator, a.analys, a.anndats, a.revdats,

a.pdf, a.value, b.*

from ibes.detu_epsus a, &INFILE b

where a.ticker=b.ticker and a.fpi in ('6','7')

and nmiss(b.&date, a.anndats)=0 and 0<=b.&date-a.anndats<=&WINDOW

and a.fpedats=b.&datadate;

/* Count number of estimates reported on primary/diluted basis */

create table ibes

as select a.*, sum(pdf='P') as p_count, sum(pdf='D') as d_count

from ibes_temp a

group by ticker, fpedats

order by ticker, fpedats, estimator, analys, anndats, revdats;

quit;

/* Determine whether most analysts report estimates on primary/diluted */

/* basis following Livnat and Mendenhall (2006) */

data ibes; set ibes;

by ticker fpedats estimator analys;

if nmiss(p_count, d_count)=0 then do;

if p_count>d_count then basis='P'; else basis='D'; end;

if last.analys; /* Keep the latest observation for a given analyst */

run;

/* Link Unadjusted estimates with Unadjusted actuals */

proc sql;

create table ibes1

as select a.*, b.anndats as repdats, b.value as act

from ibes a left join ibes.actu_epsus b

on a.ticker=b.ticker and a.fpedats=b.pends and b.pdicity='QTR';

/* select all relevant combinations of Permnos and Date */

create table ibes_anndats

as select distinct permno, anndats

from ibes1

union

select distinct permno, repdats as anndats

from ibes1;

/* Adjust all estimate and earnings announcement dates to the closest */

/* preceding trading date in CRSP to ensure that adjustment factors wont */

/* be missing after the merge */

create view tradedates

as select a.permno, a.anndats, b.date format=date9.

from (select distinct permno, anndats from ibes_anndats

where not missing(anndats)) a

left join (select distinct date from crsp.dsi) b

on 0<=a.anndats-b.date<=15

group by a.permno, a.anndats

having a.anndats-b.date=min(a.anndats-b.date);

/* Merge the CRSP adjustment factors for all estimate and report dates */

create table ibes_anndats1

as select a.*, c.cfacshr

from ibes_anndats a left join tradedates b

on a.permno=b.permno and a.anndats=b.anndats

left join crsp.dsf (keep=permno date cfacshr) c

on a.permno=c.permno and b.date=c.date;

/* Put the estimate on the same per share basis as */

/* company reported EPS using CRSP Adjustment factors. New_value is the */

/* estimate adjusted to be on the same basis with reported earnings */

create table ibes2

as select a.*, (c.cfacshr/b.cfacshr)*a.value as new_value

from ibes1 a, ibes_anndats1 b, ibes_anndats1 c

where (a.permno=b.permno and a.anndats=b.anndats)

and (a.permno=c.permno and a.repdats=c.anndats);

quit;

/* Sanity check: there should be one most recent estimate for */

/* a given firm-fiscal period end combination */

proc sort data=ibes2 nodupkey; by ticker fpedats estimator analys; run;

/* Compute the median forecast based on estimates in the specified days */

/* prior to the selected date (DATE) */

proc means data=ibes2 noprint;

by ticker fpedats; id basis;

var new_value; id repdats act permno;

output out= consensus (drop=_type_ _freq_)

median=medest mean=meanest n=numest;

run;

/* Merge INFILE with IBES consensus */

proc sql; create table &OUTFILE

as select distinct a.*, b.medest, b.meanest, b.numest, b.act, b.basis

from &INFILE a left join consensus b

on a.ticker=b.ticker and a.&datadate=b.fpedats;

quit;

proc sql;

drop view ibes_temp, tradedates;

drop table ibes, ibes1, ibes2, ibes_anndats, ibes_anndats1, consensus;

quit;

options errors=&errors &oldoptions;

%MEND;

SAS macro to get analysts EPS consensus for a given fiscal period end (DATADATE) by a selected date (DATE)

Categories

Archives

Site Admin