03-26-2016 05:07 AM
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.
03-26-2016 08:16 AM
03-26-2016 05:32 AM
03-26-2016 05:38 AM
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.
03-26-2016 05:41 AM
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?
03-26-2016 07:01 AM
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.
03-26-2016 11:16 PM
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
03-26-2016 08:16 AM
03-26-2016 11:54 PM
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.
03-27-2016 01:41 AM
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!
03-27-2016 08:45 AM
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?
03-27-2016 08:58 AM
I formatted the variable CUSIP in the CMPST_INT data table in a Data step, not in the MERGE statement:
Format CUSIP $8. Date yymmddn8.;
Need further help from the community? Please ask a new question.