Hello Everyone,
I am writing a SAS program where I get data from Compustat and CRSP in order to do an event study. I merged my CRSP stock return data with my CRSP market index return data with no issues. However, I am having issues merging my Compustat data with my CRSP data - for some reason, the match merge is not working, and it does not give me any error messages. Can someone please help me?
Here is my SAS program - it uses remote connection to WRDS. The issue is with the match merge at the end - in red font color.
Thanks for your quick reply. When I mean not working is that I get no matches. The reason that I added the RETAIN in the last crsp_stk_indx step was to re-order the columns in the data table so that Date column would appear before the CUSIP column, which is the same way as the cmpst_int data table. However, if I remove these re-ordering steps, I still do not get any matches. I troubleshooted the program last night for quite a few hours and cannot figure out why there are no matches.
I appreciate your help to see if you have any other thoughts on why I don't have any matches.
Thanks!
Yes, no matches. I took out the RETAIN steps, and I still do not get any matches. I troubleshooted the program extensively and I cannot figure it out. Can you please let me know your thoughts?
Reordering should not cause the problem at least not the code you used.
Clearly there are no combinations of CUSPID DATE values that match (call me Mr. Obvious)
If it ain't the program it must be the data.
Excellent suggestions @data_null__. I would suggest using ROUND(DATE) instead of INT(DATE) to fix the Excel date problem as I have seen Excel (or MS-Access) dates being imported with values such as 12345.999998
Thanks everyone for your suggestions. One key point: The data is not coming from Excel; if you look at the SAS code, I am connecting remotely to WRDS to get Compustat and CRSP data directly, and then bringing it into SAS. Therefore, this is not the issue.
I will try the other suggestions, specifically the PROC CONTENTS and PROC FREQ to see if there are any issues with the data formats, and wil let you know.
Thanks!
Hi Everyone,
Thanks again for your great suggestions. I ran PROC CONTENTS and uncovered that CUSIP in CRSP_STK_INDX data table was not the same format as CUSIP in CMPST_INT data table. Once I made the format change, the match merge now works!
Thank you again for your great help!
MERGE does not use formatted value of a BY variable to do matching (unless the GROUPFORMAT option is used on the BY statement). What did you actually change?
Hi data_null_,
I formatted the variable CUSIP in the CMPST_INT data table in a Data step, not in the MERGE statement:
Data CMPST_INT;
Format CUSIP $8. Date yymmddn8.;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: