Conducting a Web Query Through WRDS of the CRSP Database

Ec 485 REVISED

Spring 2007

This document details the necessary steps to download CRSP data and Fama-French data from WRDS, and to merge the two datasets into a single data set.

Conducting a Web Query through WRDS of the monthly CRSP database

§  Log into the WRDS web site http://wrds.wharton.upenn.edu. Click on Members Login and use the class username and password given out in class.

§  After logging in, you will eventually reach the main Wharton Research Data Service page. At the top of the page, there is a drop down menu titled “database”. Drop it down and choose CRSP (not CRSPQ).

§  From this next page, go to the upper left where it says "Annual Updates", choose Monthly Stocks.

This brings you to the basic web query page. You need to tell WRDS what data to extract for you: the timeframe (date range), the list of companies, the variables (data) for these companies, and finally how to output these data into a useable dataset.

Follow the 4 steps on this Web Query page:

Step One: Date Range: Choose Monthly, and then specify your date range.

Step Two: Search: You have a choice of what identifier to use for searching. It is easiest to begin with a set of firms that are identified by their stock ticker symbol. If you have just a few firms, list their ticker symbols in the space provided, separated by spaces. If you have a large number of firms, read the on-line directions for creating a .txt file that contains the ticker symbols, one per row.

Step Three: Variables …for this example, choose the following

Identifying Information

ü  CUSIP

ü  Company Name

ü  TICKER

ü  SIC Code

Time Series Information

ü  Holding Period Return (this is the monthly rate of return inclusive of dividends and adjusted for splits)

Market Information (NYSE/AMEX/Nasdaq)

ü  Value-Weighted Return (includes distributions)

Step Four: Output: There are several different formats to choose. Since you will be analyzing the data in SAS, it is easiest to choose the SAS Windows format. This creates a permanent SAS dataset.

SAS Windows_32 dataset (*.sas7bdat)

Click on SUBMIT REQUEST. Pay attention and be patient. WRDS initially tells you the request has been submitted and is being processed. When it is ready, a link appears that contains your file. WRDS gives it a filename that ends with .sas7bdat. Right click on this link and save it to your hard-drive, key drive, etc. I suggest that you rename the first part of this file name, such as mycrspdata.sas7bdat (file name must end with .sas7bdat).

Conducting a Web Query through WRDS of the Fama-French database

§  Log into the WRDS web site.

§  At the top of the page, there is a drop down menu titled “database”. Drop it down and choose Fama French, Momentum, Liquidity dataset

§  From this next page, go to the upper left where it says "Fama-French Portfolios", choose “Factors”.

This brings you to the basic web query page. Basically, you need to tell WRDS what data to extract for you: the timeframe (date range), the factors (variables), and finally how to output these data into a useable dataset.

Follow the 4 steps on this Web Query page:

Step One: Data Frequency: Choose Monthly,

Step Two: Date Range. It should be the same date range as your CRSP data set.

Step Three: Factors: Choose your factors. For our project, you will need only one:

ü  Risk-Free Interest Rate (One Month Treasury Bill Rate) (RF)

Step Four: Output. There are several different formats to choose. Since you will be analyzing the data in SAS, it is easiest to choose the SAS Windows format. This creates a permanent SAS dataset.

SAS Windows_32 dataset (*.sas7bdat)

Choose DATE9. for the Date Format. It is the second option on the drop down menu for Date.

Compression Type: <none>

Click on SUBMIT REQUEST. Pay attention and be patient. WRDS initially tells you the request has been submitted and is being processed. When it is ready, a link appears that contains your file. WRDS gives it a filename that ends with .sas7bdat. Right click on this link and save it to your hard-drive, key drive, etc. I suggest that you rename the first part of this file name, such as myRFdata.sas7bdat (file name must end with .sas7bdat).

Combining the Two Data Sets

We now have two permanent SAS datasets that need to be merged into one dataset. The variable DATE will be used to do the merging. Type in the following code in the SAS editor window, and run it. You will need to modify the path in the libname statement…notice that this is a path and not a path plus file. It tells SAS where to find your permanent SAS dataset named mycrspdata.sas7bdat. Notice that the second part of this file name is not needed in the set command.

options ls=78 formdlim='*';

libname mydat 'C:\Documents and Settings\doylejm\My Documents\Classes\Ec485' ;

data one;

set mydat.mycrspdata;

proc contents;

proc sort;

by date;

run;

data two;

set mydat.myRFdata;

proc contents;

proc sort;

by date;

run;

data three;

merge one two;

by date;

proc print data=three(obs=100);

run;

proc sort out=junk nodupkey;

by permno ;

run;

proc print data=junk;

var permno comnam ticker;

run;

WARNING: at this point in the code, the data set in memory is JUNK. Since you created this data set for a one-time use (to get a table of PERMNOs, company names, and tickers) you should delete these blocks of code so that the data set in memory is THREE and so that you can add more code to this program to carry out the necessary regressions.