Downloading IBES data for a list of CUSIPs from a file

[Diep Nguyen]

Hi all, I want to download NUMEST from IBES for a list of CUSIPs, which I store in a text file. The time period is from 2000-2010.

I would like to ask how I can tell SAS to do this. My guess is to extract a portion of the ibes.STATSUM_EPSUS file then download it to my PC. But I don't really know how to code this. My questions are underscored. These codes illustrate my ideas only:

rsubmit;
DATA myibes.STATSUM_EPSUS;
  set ibes.STATSUM_EPSUS (keep=cusip numest);
  where CUSIP in (HOW TO SPECIFY A LIST OF CUSIPS HERE???);
  where year in HOW TO SPECIFY THE YEAR HERE???
RUN;
endrsubmit;

libname mylib 'D:\...';
rsubmit;
proc download data=myibes.STATSUM_EPSUS
    out=mylib.USibes;
run;
endrsubmit;

I appreciate your help!

DN

[joost impink]

hi Diep,

I put a macro online that is close to what you need: http://www.wrds.us/index.php/repository/view/35

It is assuming that you have the WRDS datasets local, but you can 'move' the code into a rsubmit-endrsubmit block. 

To answer your question on how to 'mix in' your own datasets, you can use proc upload' to use a local dataset in queries on WRDS, like so:

rsubmit;

proc upload data=work.dsin out=myData;

/* I have rewritten your data step in proc sql, because of my love for/addiction to sql */

proc sql;

  create table myTable as

select a.*, b.* from ibes.statsum_epses a, myData

where a.cusip = b.cusip and a.year = b.year; /* this won't work by the way, just for illustration -- you need more code to get NUMEST, see the link above */

quit;
endrsubmit;

Best regards,

Joost

[Diep Nguyen]

Hi Joost, thanks for your suggestion! I was able to do the following, which is very simple but I think it works for my purpose. I post this to benefit other beginners like me and to spot mistakes if you see one.

%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=TCP remote=WRDS;
signon username=_prompt_;

libname myibes 'D:\...'; /*specify a library for yourself*/
/*Import the text file tickers, which has the list of tickers of the companies in an n rows x 2 columns format*/
/*first row has names of variables: ticker and year*/
proc import datafile="D:\...\tickers.txt"
     out=myibes.tickers
     dbms=tab replace;
     getnames=yes;
run;

/*check the imported data*/
proc print data=tickers;
run;


/*Upload the tickers file onto the server*/
rsubmit;
proc upload data=myibes.tickers out=tickers;
endrsubmit;


/*See the content of the data file in IBES*/
rsubmit;
 libname ibes '/wrds/ibes/sasdata';
 proc contents data=ibes.STATSUM_EPSUS;
 run;
endrsubmit;

/*create a table as matching data from IBES with the tickers and the years*/
rsubmit;

proc sql;
  create table myTable as

     select a.*, b.*, /*load all columns of the two files*/
            year(a.STATPERS) as DataYear, /*so you see the year of the data*/
            month(a.STATPERS) as DataMonth /*so you see the month of the data*/
         from ibes.STATSUM_EPSUS a, tickers b
         where a.OFTIC = b.ticker and year(a.STATPERS)=b.Year;
         /*here we match OFTIC from ibes.STATSUM_EPSUS with
         tickers from tickers*/
endrsubmit;

rsubmit;
proc print data=myTable (obs=10); /*print first 10 observations to check*/
run;
endrsubmit;


/*Download the data*/
libname myibes 'D:\...\IBES';
rsubmit;
proc download data=myTable
    out=myibes.FinalData;
run;
endrsubmit;

My question is: what does your macro in http://www.wrds.us/index.php/repository/view/35 do besides than downloading NUMEST from IBES? I think that you also clean the data by deleting missing records and deleting duplicates but you may explain it better than myself. I am pretty new to SAS so I can't read your code as fast but it's good to know what you do differently. Thanks a million!

DN

[joost impink]

hi Diep,

Thanks for posting the code.

The main difference between your code and the macro is the 'join' on IBES

In your code: 

from ibes.STATSUM_EPSUS a, tickers b where a.OFTIC = b.ticker and year(a.STATPERS)=b.Year;

In the macro:

      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

The reason why there are more requirements is that the IBES table holds forecasts for different periods (multiple quarters, years), multiple points in time (monthly), for different 'things' (EPS, sales, etc). These get filtered out this way. It gets the number of analysts for the last month of the fiscal year. So it returns a single number (your code will return multiple numbers), which may be a problem if they are rather different :)

best regards,

Joost

[Diep Nguyen]

Hi Joost, thanks for the clarification. I guess that your variable datadate is the last day of the year when you want to have the data on the number of analysts?

DN

[joost impink]

hi Diep,

Yes, IBES summary files has monthly data; so somehow you need to decide which month to use. The macro it uses the last month of the fiscal year.

best regards,

Joost

[Diep Nguyen]

Thank you Joost!

[Diep Nguyen]

Hi Joost, just to follow up on this topic. I realize in my data that for each company, you can have different number of analysts following if you retrieve EPS estimates. The problem is that each firm may have different share classes, and different numbers of analysts submitting estimates for each share class.
For example the following data:

ticker        cname               statpers        fpi    numest    fpedats
ACB1/1    AIR CANADA    20sep2012    1       4            31dec2012
ACB1       AIR CANADA    20sep2012    1       8            31dec2012

So two numbers for numest for Air Canada you can use are 4 or 8 depending on the share class you are talking about.
I don't know if this issue has been raised somewhere but FYI.

DN

On Tuesday, August 12, 2014 11:27:24 PM UTC-4, joost impink wrote:

[joost impink]

hi Diep,

Thanks for following up!

In case you would want to filter on shares with multiple classes, there is a flag on ibes.idsum (variable INSTRMNT, Instrument Type Flag). 

For 'Air Canada', the ticker 'ACB1/1' has this flag set to M (=multiple shares); for 'ACB1' it is set to S (= Security). This could be helpful in getting rid of 'class B' shares, etc.

best regards,

Joost