{"id":371,"date":"2015-08-17T21:45:39","date_gmt":"2015-08-18T01:45:39","guid":{"rendered":"http:\/\/csclub.uwaterloo.ca\/~k55chen\/?p=371"},"modified":"2019-04-28T14:30:58","modified_gmt":"2019-04-28T18:30:58","slug":"sas-macro-get-analysts-eps-consensus-for-a-given-fiscal-period-end-datadate-by-a-selected-date-date","status":"publish","type":"post","link":"https:\/\/www.kaichen.work\/?p=371","title":{"rendered":"SAS macro to get analysts EPS consensus for a given fiscal period end (DATADATE) by a selected date (DATE)"},"content":{"rendered":"<p><strong>Update<\/strong>: contains error! will update later.<\/p>\n<p>I write\u00a0this macro to\u00a0compute\u00a0analysts&#8217; quarterly EPS consensus on a selected date (DATE) for a given\u00a0fiscal quarter end (DATADATE). This macro can be easily modified for other types of estimates (e.g., annual EPS).<\/p>\n<p>This macro currently extracts unadjusted quarterly EPS estimates\u00a0(current or next quarter) issued within a specified window (e.g., WINDOW = 60 days) before the\u00a0selected date (DATE), and computes the consensus at median and mean. This macro also extracts unadjusted\u00a0actual EPS and put estimate and actual on the same per share\u00a0basis. Please see <a href=\"https:\/\/wrds-web.wharton.upenn.edu\/wrds\/E-Learning\/_000Course%20Materials\/Overview%20of%20Thomson%20IBES.pdf.cfm?\" target=\"_blank\" rel=\"noopener noreferrer\">Overview of Thomson-Reuters IBES<\/a> for why this is the preferred method.<\/p>\n<p>The output includes the mean and median of analysts&#8217; EPS forecasts, the number of analysts forecasts, and the actual EPS. Please note the actual EPS is &#8220;street&#8221; EPS from I\/B\/E\/S and may be different from reported EPS from Compustat (see <a href=\"https:\/\/wrds-web.wharton.upenn.edu\/wrds\/support\/Additional%20Support\/WRDS%20Knowledge%20Base%20with%20FAQs.cfm?folder_id=653&amp;article_id=1358\" target=\"_blank\" rel=\"noopener noreferrer\">I\/B\/E\/S FAQ<\/a>). With these outputs, you are able to compute earnings surprises easily.<\/p>\n<p>This macro is modified from <a href=\"https:\/\/wrds-web.wharton.upenn.edu\/wrds\/support\/Data\/_004Research%20Applications\/_001Portfolio%20Construction%20and%20Benchmarks\/_005Post-Earnings%20Announcement%20Drift\/Overview%20and%20Code.cfm\" target=\"_blank\" rel=\"noopener noreferrer\">Post-Earnings Announcement Drift<\/a>\u00a0program on WRDS. I thank the author, Denys Glushkov, who is my favorite\u00a0SAS programmer.<\/p>\n<p>I looked into I\/B\/E\/S database more closely\u00a0and noted\u00a0a few facts:<\/p>\n<ul>\n<li>Since 1998 only 1.3% of analysts&#8217; forecasts are reported on the primary basis (v.s. 74.4% before 1998), making the variable `basis` not important any more;<\/li>\n<li>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.<\/li>\n<li>`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 <a href=\"https:\/\/wrds-web.wharton.upenn.edu\/wrds\/support\/Additional%20Support\/WRDS%20Knowledge%20Base%20with%20FAQs.cfm?folder_id=653&amp;article_id=1757\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>).<\/li>\n<\/ul>\n<pre class=\"lang:sas decode:true \"> %MACRO CONSENSUS(INFILE=, DATE=, DATADATE=, WINDOW=, OUTFILE=);\r\n\r\n\/* This macro is used to compute analysts' quarterly EPS consensus on a selected date  *\/\r\n\/* for a given fiscal quarter end (DATADATE). This macro can be easily modified for    *\/\r\n\/* other types of estimates (e.g., annual EPS).                                        *\/\r\n\r\n\/* This macro currently extracts unadjusted quarterly EPS estimates (current or next   *\/\r\n\/* quarter) issued within a specified window (e.g., WINDOW = 90 days) before the       *\/\r\n\/* selected date (DATE), and computes the consensus at median and mean. This macro     *\/\r\n\/* also extracts unadjusted actual EPS and put estimate and actual on the same per     *\/\r\n\/* share basis.                                                                        *\/\r\n\r\n\/* This macro also creates a variable \"basis\" to indicate whether most analysts        *\/\r\n\/* report estimates on primary\/diluted basis. This variable is then useful when        *\/\r\n\/* comparing analyst consensus with Compustat\u00eds primary (EPSPXQ) or diluted (EPSFXQ)   *\/\r\n\/* EPS, e.g., in earnings surprise study. A noticeable fact is that since 1998 only    *\/\r\n\/* 1.3% of analysts' forecasts are reported on primary basis in I\/B\/E\/S (versus 74.4%  *\/\r\n\/* before 1998), rendering the variable \"basis\" not meaningful any more. This macro    *\/\r\n\/* still creates the variable though.                                                  *\/\r\n\r\n\/* INFILE should contain IBES Ticker, PERMNO, DATE and DATADATE. OUTFILE includes the  *\/\r\n\/* mean and median of analysts' EPS forecasts (MEDEST and MEANEST), the number of      *\/\r\n\/* analysts' forecasts (NUMEST), and the actual EPS (ACT).                             *\/\r\n\r\n %local oldoptions errors;\r\n %let oldoptions=%sysfunc(getoption(mprint)) %sysfunc(getoption(notes))\r\n                %sysfunc(getoption(source));\r\n %let errors=%sysfunc(getoption(errors));\r\n options notes mprint source errors=0;\r\n\r\n\/* Extract estimates from IBES Unadjusted file and keep only       *\/\r\n\/* the estimates issued within specified days before the selected  *\/\r\n\/* date \"fpi in (6,7)\" selects quarterly forecast for the current  *\/\r\n\/* and the next fiscal quarter                                     *\/ \r\nproc sql;\r\n  create view ibes_temp\r\n    as select a.fpedats, a.estimator, a.analys, a.anndats, a.revdats, \r\n        a.pdf, a.value, b.*\r\n    from ibes.detu_epsus a, &amp;INFILE b\r\n    where a.ticker=b.ticker and a.fpi in ('6','7')\r\n        and nmiss(b.&amp;date, a.anndats)=0 and 0&lt;=b.&amp;date-a.anndats&lt;=&amp;WINDOW\r\n        and a.fpedats=b.&amp;datadate;\r\n\r\n\/* Count number of estimates reported on primary\/diluted basis *\/\r\n  create table ibes\r\n    as select a.*, sum(pdf='P') as p_count, sum(pdf='D') as d_count\r\n    from ibes_temp a\r\n    group by ticker, fpedats\r\n  order by ticker, fpedats, estimator, analys, anndats, revdats;\r\nquit;\r\n\r\n\/* Determine whether most analysts report estimates on primary\/diluted *\/\r\n\/* basis following Livnat and Mendenhall (2006)                        *\/       \r\ndata ibes; set ibes;\r\n  by ticker fpedats estimator analys;\r\n  if nmiss(p_count, d_count)=0 then do;\r\n  if p_count&gt;d_count then basis='P'; else basis='D'; end;\r\n  if last.analys; \/* Keep the latest observation for a given analyst *\/\r\nrun;\r\n\r\n\/* Link Unadjusted estimates with Unadjusted actuals *\/\r\nproc sql;\r\n  create table ibes1\r\n    as select a.*, b.anndats as repdats, b.value as act \r\n    from ibes a left join ibes.actu_epsus b\r\n    on a.ticker=b.ticker and a.fpedats=b.pends and b.pdicity='QTR';\r\n    \r\n\/* select all relevant combinations of Permnos and Date *\/\r\n  create table ibes_anndats\r\n    as select distinct permno, anndats\r\n    from ibes1\r\n    union\r\n    select distinct permno, repdats as anndats\r\n    from ibes1;\r\n    \r\n\/* Adjust all estimate and earnings announcement dates to the closest    *\/\r\n\/* preceding trading date in CRSP to ensure that adjustment factors wont *\/\r\n\/* be missing after the merge                                            *\/\r\n  create view tradedates\r\n    as select a.permno, a.anndats, b.date format=date9.\r\n    from (select distinct permno, anndats from ibes_anndats\r\n          where not missing(anndats)) a\r\n    left join (select distinct date from crsp.dsi) b\r\n    on 0&lt;=a.anndats-b.date&lt;=15\r\n    group by a.permno, a.anndats\r\n    having a.anndats-b.date=min(a.anndats-b.date);\r\n    \r\n\/* Merge the CRSP adjustment factors for all estimate and report dates   *\/\r\n    create table ibes_anndats1\r\n    as select a.*, c.cfacshr\r\n    from ibes_anndats a left join tradedates b\r\n    on a.permno=b.permno and a.anndats=b.anndats\r\n    left join crsp.dsf (keep=permno date cfacshr) c\r\n    on a.permno=c.permno and b.date=c.date;\r\n    \r\n\/* Put the estimate on the same per share basis as *\/\r\n\/* company reported EPS using CRSP Adjustment factors. New_value is the  *\/\r\n\/* estimate adjusted to be on the same basis with reported earnings      *\/\r\n    create table ibes2\r\n    as select a.*, (c.cfacshr\/b.cfacshr)*a.value as new_value\r\n    from ibes1 a, ibes_anndats1 b, ibes_anndats1 c\r\n    where (a.permno=b.permno and a.anndats=b.anndats)\r\n      and (a.permno=c.permno and a.repdats=c.anndats);\r\nquit;\r\n    \r\n\/* Sanity check: there should be one most recent estimate for *\/\r\n\/* a given firm-fiscal period end combination                 *\/\r\nproc sort data=ibes2 nodupkey; by ticker fpedats estimator analys; run;\r\n\r\n\/* Compute the median forecast based on estimates in the specified days  *\/\r\n\/* prior to the selected date (DATE)                                     *\/\r\nproc means data=ibes2 noprint;\r\n   by ticker fpedats; id basis;\r\n   var new_value; id repdats act permno;\r\n   output out= consensus (drop=_type_ _freq_)        \r\n   median=medest mean=meanest n=numest;\r\nrun;\r\n \r\n\/* Merge INFILE with IBES consensus *\/\r\nproc sql; create table &amp;OUTFILE\r\n  as select distinct a.*, b.medest, b.meanest, b.numest, b.act, b.basis\r\n  from &amp;INFILE a left join consensus b\r\n  on a.ticker=b.ticker and a.&amp;datadate=b.fpedats;\r\nquit;\r\n\r\nproc sql;\r\n  drop view ibes_temp, tradedates;\r\n  drop table ibes, ibes1, ibes2, ibes_anndats, ibes_anndats1, consensus;\r\nquit;\r\n\r\noptions errors=&amp;errors &amp;oldoptions;\r\n%MEND;\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Update: contains error! will update later. I write\u00a0this macro to\u00a0compute\u00a0analysts&#8217; quarterly EPS consensus on a selected date (DATE) for a given\u00a0fiscal quarter end (DATADATE). This macro can be easily modified for other types of estimates (e.g., annual EPS). This macro &hellip; <a href=\"https:\/\/www.kaichen.work\/?p=371\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"_links":{"self":[{"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/posts\/371"}],"collection":[{"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=371"}],"version-history":[{"count":37,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/posts\/371\/revisions"}],"predecessor-version":[{"id":1248,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/posts\/371\/revisions\/1248"}],"wp:attachment":[{"href":"https:\/\/www.kaichen.work\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=371"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=371"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=371"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}