Identifying Company “ID Keys” in WRDS
Posted: 27 September 2011 12:25 PM   [ Ignore ]
Total Posts:  4
Joined  2011-09-27

Hi all,

Starting to work on some VERY basic SAS tutorials (“baby steps”), essentially expanding on an in-class exercise performed w/ GOOG.

My first adventure, replicate the exercise w/ more companies and more variables in COMPUSTAT… mainly to satisfy my own curiosity and dig into some financials.

Is the best way to do this:

Support (Tab) -> Company Search (under “Tools and References”)

For now, I’m just starting w/ COMPUSTAT data, so I just tick off that box, follow the instructions (search by name, ticker name, etc.), and go from there?

Is there a better way to do this? For example, is there one step to do something like pull these ID Keys for every company in the DOW, or S&P 500, etc. all at once? Or pull industries?



Posted: 27 September 2011 01:52 PM   [ Ignore ]   [ # 1 ]
Total Posts:  901
Joined  2011-09-19

hi Matt,

Thanks for your question smile

To retrieve the same info for firms of the S&P 500 (for example), we use two tables on WRDS that contain the firms that are in the various indices:

NAMES_IX - holds the names of the indices (there are 1957 indices!)
IDXCST_HIS (‘Index Constituent History’)- holds the firms that are in the idices.

The following code downloads these tables to the work directory.

%let wrds = 4016;options comamid = TCP remote=WRDS;
signon username=_prompt_;


libname comp ‘/wrds/comp/sasdata/naa/index’; 

proc download data=comp.NAMES_IX   out=work.NAMES_IX;run;
proc download data=comp.IDXCST_HIS out=work.IDXCST_HIS;run;


Looking at ‘NAMES_IX’ tells us that the gvkeyx for the S&P 500 Index (‘S&P 500 Comp-Ltd’) is ‘000003’.

Companies that were part of the S&P 500 will have gvkeyx=‘000003’ in IDXCST_HIS. This table holds firms (gvkey) that were part of the index between ‘from’ and ‘thru’. If ‘thru’ is missing, it means the company is still part of the index.

Lets create subset of this dataset that holds all S&P firms, and set thru to a future date (January 1, 2050) when missing. We can then select firm-years where datadate (end of year date) is within ‘from’ and ‘thru’.

data sp500;
if gvkeyx eq “000003” ;
if thru eq . then thru = mdy(1,1,2050); * replacing ‘missing’ (still in index) with a future date;

The dataset ‘sp500’ holds 1576 gvkeys. This is more than 500, because of firm moving in and out of the index.

Next, we retrieve Compustat data for firms in our sp500 dataset for fiscal years between 2004 and 2010.

So, we upload ‘sp500’ to match with Compustat funda.
We match on 3 fields:
- funda.gvkey = sp500.gvkey (Firms need to match)
- 2004 <= funda.fyear <= 2010 (Firm years in range)
- sp500.from <= funda.datadate <= sp500.thru (Firm needs to be part of S&P)
And download the result.

The code:

libname comp ‘/wrds/comp/sasdata/naa’;  * notice this is a different library ;

proc upload data=sp500 out=sp500;run;

proc sql;

  create table sp500comp (keep = gvkey gvkeyx from thru datadate fyear sich ceq prcc_f csho ni at) as
  select a.*, b.* 
  from sp500 a, comp.funda b
      a.gvkey = b.gvkey
and 2004 <= b.fyear <= 2010
and a.from <= b.datadate <= a.thru
  and indfmt=‘INDL’ and datafmt=‘STD’ and popsrc=‘D’ and consol=‘C’ ; 

proc download data=sp500comp out=work.sp500comp;run;


* The resulting dataset holds 3491 observations. Let’s verify if there are about 500 observations for each fiscal year;

proc sql; select fyear, count(*) as numObs from sp500comp group by fyear; quit;

The number of observations for each firm-year ranges between 497 and 501.

Hope this helps,



To reply/post new questions: Please use the group WRDS/SAS on Google Groups!

Posted: 28 September 2011 03:23 PM   [ Ignore ]   [ # 2 ]
Total Posts:  4
Joined  2011-09-27

Thanks, Joost!

Very thorough!


     introduction to wrds ››