%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;
