1. Wrds data list and sample programs
2. Merge CRSP with Compustat data
Code to merge Compustat and CRSP
NOTE: This code is updated to reflect the change in CRSP and Compustat. The macro _mycstlink.sas creates a dataset that is helpful in merging Compustat and CRSP. It addresses the issues discussed in Beaver, McNichols and Price (2007). This sample program shows how to use the program.
Here is a legacy version of _mycstlink.sas and the sample program based on the old FTP format of Compustat.
Accounting/Finance Research SAS Programs
Q: How can I merge data from the CRSP and Compustat sets if our institution does not current subscribe to the CRSP/Compustat merged database?
Wrds has a sample program merge_funda_crsp_bycusip.sas demonstrating how to merge CRSP and Compustat data using CUSIP.
2. Merge CRSP with Compustat data
Here is a typical code segment to access the COMP.FUNDA. From: Qin Lei's website
The most useful combination of these keys requires DATAFMT='STD' and INDFMT='INDL' and CONSOL='C' and POPSRC='D' to retrieve the standardized (as opposed to re-stated data), consolidated (as opposed to pro-forma) data presented in the industrial format (as opposed to financial services format) for domestic companys (as opposed to international firms), i.e., the U.S. and Canadian firms.
3. Merge CRSP/Compustat data with IBES data
This program is intended for calculation of quarterly standardized earnings surprises (SUE) based on time-series
(seasonal random walk model) and analyst EPS forecasts. The short and intermediate-term risk-adjusted returns
associated with the earnings announcements are also calculated. The analysis is based on Livnat and Mendenhall
(Journal of Accounting Research, Vol. 44, No.1, March 2006) "Comparing the Post-Earnings Announcement Drift for
Surprises Calculated from Analyst and Time Series Forecasts".
Samples of Output generated by this program ( /wrds/ibes/samples/Esurprises.sas)
IBES summary data problems
Dataset List: I/B/E/S (IBES)
SAS-Code to generate earnings surprise data and earnings announcement day returns - Screenshot of Dataset (only shows earnings surprise data)
- As used in Gulen + Hwang ("Daily Stock Market Swings and Investors' Reaction to Firm-Specific News", Working Paper)
Q: I am wondering how to merge ibes with compustat by cusip. I noticed that in compustat cusip is a 9 digit variable while in ibes cusip is an 8 digit variable. Could you tell me how reconcile them?
A: The main problem of linking Compustat with IBES is not the fact that Compustat's cusip is 9 character, whereas IBES is 8-character. The main issue is that Compustat Cusip is header (most recent), whereas IBES Cusip is hsitorical (as of date).
WRDS has a few research applications intended to demonstrate possible approaches that can be used in order to merge Compustat GVKEY to IBES ticker. First, Compustat provides a linking header table between GVKEY and IBES ticker (IBTIC) in its SECURITY table (located in /wrds/comp/sasdata/na/security/).
If you want a more comprehensive map between GVKEY and IBES Ticker, check out on of the recent research applications on WRDS (P/E Ratio), which demonstrates how to obtain a linking table between GVKEY and IBES Ticker using CRSP-Compustat Merged product as well as WRDS ICLINK product).
Merge comp with ibes
4. Intro to WRDS and SAS: Compustat example (includes PowerPoint)
Market to book Ratio: mtb = prcc_f * csho / ceq;
Return of Asset: if mtb ne .;roa = ni /at;if roa ne .;
5. WRDS--CRSP & IBES link
Earnings' surprise link
%include '/wrds/ibes/samples/iclink.sas'; *build CRSP-IBES permno-ticker link;
score in (0,1,2); is considered to be direct match.
6. Fama McBeth regression in SAS
7. Jones model to estimate earnings management
8. Moderation & Mediation regression (lecture, sas Code)
10. Testing the equality of two or more proportions from independent samples
11. Difference in proportion test program (Key Words: SAS code Compare two population proportion Z test)
12. Performing multiple comparisons or tests on subtables following a significant Pearson chi-square
13. Local and Global Optimal Propensity Score Matching
14. Examples of writing CONTRAST and ESTIMATE statements
15. CONTRAST and ESTIMATE Statements Made Easy: The LSMESTIMATE Statement
16. Match Compustat (Funda) with Audit Analytics (Auditfees) (modified version)
17. Commonly used Unix Commands
18. UTA thesis template
19. Recent UTA placement
20. SAS macro for correlation matrix
21. SAS-Code to compute White (1980) standard errors adjusted for clustering along two dimensions written by
John McInnis (https://webspace.utexas.edu/johnmac/www/), slightly modified by me.
22. Clustered Standard Errors – Two dimensions SAS does not contain a routine to do this, but one has been
written by John McInnis. He has posted the SAS code for estimating standard errors clustered on two
dimensions on his web site.
23. Notes on Clustering, Fixed Effects, and Fama-MacBeth regressions in SAS (example.txt,Robust Inference with
Clustered Data deck)
24. A Note on the Estimation of the Multinomial Logistic Model with Correlated Responses in SAS
25. Clustered Standard Errors – Two dimensions SAS does for logistic regression
NOTE: This code is updated to reflect the change in CRSP and Compustat. The macro _mycstlink.sas creates a dataset that is helpful in merging Compustat and CRSP. It addresses the issues discussed in Beaver, McNichols and Price (2007). This sample program shows how to use the program.
Here is a legacy version of _mycstlink.sas and the sample program based on the old FTP format of Compustat.
Accounting/Finance Research SAS Programs
Q: How can I merge data from the CRSP and Compustat sets if our institution does not current subscribe to the CRSP/Compustat merged database?
Wrds has a sample program merge_funda_crsp_bycusip.sas demonstrating how to merge CRSP and Compustat data using CUSIP.
2. Merge CRSP with Compustat data
Here is a typical code segment to access the COMP.FUNDA. From: Qin Lei's website
The most useful combination of these keys requires DATAFMT='STD' and INDFMT='INDL' and CONSOL='C' and POPSRC='D' to retrieve the standardized (as opposed to re-stated data), consolidated (as opposed to pro-forma) data presented in the industrial format (as opposed to financial services format) for domestic companys (as opposed to international firms), i.e., the U.S. and Canadian firms.
3. Merge CRSP/Compustat data with IBES data
This program is intended for calculation of quarterly standardized earnings surprises (SUE) based on time-series
(seasonal random walk model) and analyst EPS forecasts. The short and intermediate-term risk-adjusted returns
associated with the earnings announcements are also calculated. The analysis is based on Livnat and Mendenhall
(Journal of Accounting Research, Vol. 44, No.1, March 2006) "Comparing the Post-Earnings Announcement Drift for
Surprises Calculated from Analyst and Time Series Forecasts".
Samples of Output generated by this program ( /wrds/ibes/samples/Esurprises.sas)
IBES summary data problems
Dataset List: I/B/E/S (IBES)
SAS-Code to generate earnings surprise data and earnings announcement day returns - Screenshot of Dataset (only shows earnings surprise data)
- As used in Gulen + Hwang ("Daily Stock Market Swings and Investors' Reaction to Firm-Specific News", Working Paper)
Q: I am wondering how to merge ibes with compustat by cusip. I noticed that in compustat cusip is a 9 digit variable while in ibes cusip is an 8 digit variable. Could you tell me how reconcile them?
A: The main problem of linking Compustat with IBES is not the fact that Compustat's cusip is 9 character, whereas IBES is 8-character. The main issue is that Compustat Cusip is header (most recent), whereas IBES Cusip is hsitorical (as of date).
WRDS has a few research applications intended to demonstrate possible approaches that can be used in order to merge Compustat GVKEY to IBES ticker. First, Compustat provides a linking header table between GVKEY and IBES ticker (IBTIC) in its SECURITY table (located in /wrds/comp/sasdata/na/security/).
If you want a more comprehensive map between GVKEY and IBES Ticker, check out on of the recent research applications on WRDS (P/E Ratio), which demonstrates how to obtain a linking table between GVKEY and IBES Ticker using CRSP-Compustat Merged product as well as WRDS ICLINK product).
Merge comp with ibes
4. Intro to WRDS and SAS: Compustat example (includes PowerPoint)
Market to book Ratio: mtb = prcc_f * csho / ceq;
Return of Asset: if mtb ne .;roa = ni /at;if roa ne .;
5. WRDS--CRSP & IBES link
Earnings' surprise link
%include '/wrds/ibes/samples/iclink.sas'; *build CRSP-IBES permno-ticker link;
score in (0,1,2); is considered to be direct match.
6. Fama McBeth regression in SAS
7. Jones model to estimate earnings management
8. Moderation & Mediation regression (lecture, sas Code)
- sas, proc logistic interaction coefficients
- sas, Contrast statements for interaction effects in Proc Logistic
- How can I compare regression coefficients between two groups?
- Models with interactions of continuous and categorical variables
10. Testing the equality of two or more proportions from independent samples
11. Difference in proportion test program (Key Words: SAS code Compare two population proportion Z test)
12. Performing multiple comparisons or tests on subtables following a significant Pearson chi-square
13. Local and Global Optimal Propensity Score Matching
14. Examples of writing CONTRAST and ESTIMATE statements
15. CONTRAST and ESTIMATE Statements Made Easy: The LSMESTIMATE Statement
16. Match Compustat (Funda) with Audit Analytics (Auditfees) (modified version)
17. Commonly used Unix Commands
18. UTA thesis template
19. Recent UTA placement
20. SAS macro for correlation matrix
21. SAS-Code to compute White (1980) standard errors adjusted for clustering along two dimensions written by
John McInnis (https://webspace.utexas.edu/johnmac/www/), slightly modified by me.
22. Clustered Standard Errors – Two dimensions SAS does not contain a routine to do this, but one has been
written by John McInnis. He has posted the SAS code for estimating standard errors clustered on two
dimensions on his web site.
23. Notes on Clustering, Fixed Effects, and Fama-MacBeth regressions in SAS (example.txt,Robust Inference with
Clustered Data deck)
24. A Note on the Estimation of the Multinomial Logistic Model with Correlated Responses in SAS
25. Clustered Standard Errors – Two dimensions SAS does for logistic regression