Category: Data management

Author resource: Joost Impink


This macro matches an input dataset with IBES summary files to retrieve the number of analysts following. The input dataset requires a unique identifier and permno. See the sample code for sample usage.

Note: this macro has not been extensively tested, so please proceed with caution.

    Created by:     Joost Impink
    Date:           March 2014
    Macro to count the number of analysts following; the number of 
	analysts is measured as the number of analysts that have issued
	a forecast for the current 	fiscal year, in the last month of 
	that fiscal year.
    Variables required:
        dsin            dataset in (needs to have key, gkvey, 
						fyear, permno and datadate)

        dsout           dataset out

	The sample below constructs key as: key = gvkey || fyear; 

        - the macro requires local access to crsp.dsenames, 
		  ibes.idsum and ibes.STATSUMU_EPSUS in a local libray
%macro numest (dsin=, dsout=);

/* keep minimum number of vars to collect number of analysts*/
data num1 (keep = key permno datadate filingdate);
set &dsin;
if permno ne .;

/* retrieve historic cusip */
  create table num2 as
  select a.*, b.ncusip
  from num1 a, crsp.dsenames b
        a.permno = b.PERMNO
    and b.namedt <= a.datadate <= b.nameendt
    and b.ncusip ne "";

/* force unique records */
proc sort data=num2 nodupkey; by key;run;

/* get ibes ticker */
  create table num3 as
  select distinct a.*, b.ticker as ibes_ticker
  from num2 a, ibes.idsum b
        a.NCUSIP = b.CUSIP
    and a.datadate > b.SDATES 

/* get number of estimates -- last month of fiscal year*/

  create table num4 as
  select a.*, b.STATPERS, b.numest as num_analysts
  from num3 a, ibes.STATSUMU_EPSUS b
        a.ibes_ticker = b.ticker
    and b.MEASURE="EPS"
    and b.FISCALP="ANN"
    and b.FPI = "1"
    and a.datadate - 30 < b.STATPERS < a.datadate 
    and a.datadate -5 <= b.FPEDATS <= a.datadate +5

/* force unique records */
proc sort data=num4 nodupkey; by key;run;

proc sql;
	create table &dsout as 
	select a.*, b.num_analysts 
	from &dsin a 
	left join num4 b 
	on a.key=b.key;

/* missing num_analysts means no analysts following */
data &dsout;
set &dsout;
if num_analysts eq . then num_analysts = 0;

/* cleanup */
proc datasets library=work;
   delete num1-num4;


Sample usage:

data sample1 (keep = key gvkey fyear datadate conm);
set comp.funda;
/* general filter to drop doubles from Compustat Funda */
if indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
/* limit to firms with more than $20 mln sales and fiscal years 2010-2012 */
if sale > 20;
if 2010 <= fyear <= 2012;
/* create key to uniquely identify firm-year */
key = gvkey || fyear; 

/* get permno */
  create table sample2 as
  select a.*, b.lpermno as permno
  from sample1 a, crsp.ccmxpf_linktable b
    where a.gvkey = b.gvkey
    and b.lpermno ne .
    and b.linktype in ("LC" "LN" "LU" "LX" "LD" "LS")
    and b.linkprim IN ("C", "P") 
    and ((a.datadate >= b.LINKDT) or b.LINKDT = .B) and 
       ((a.datadate <= b.LINKENDDT) or b.LINKENDDT = .E)   ;

/* append number of analysts */
%numest(dsin=sample2, dsout=sample3);

Other Data management
SAS Winsorize macro


Latest forum posts
Fama French 49 YES 49 !!! by CA Miller
date format by jc625
Discretionary Accruals by jwhi121
question about discretionary accrual models by kerrida
Combining Global and North American data by CA Miller
File for Eventus or SAS Event Study by samme
quarterly accruals by lxt88
Financial Statement Comparability - De Franco, Kothari and Verdi (2011, JAR) Replication by rowing
question about discretionary accrual models by Zenghui
problem in perl by KZ

All rights reserved. © 2010-2014 [Copyright] [Privacy Statement] [Disclaimer] [About]