Hello,
I want to merge CRSP trading days with Compustat event dates.
As you are aware, CRSP daily data file does not contain information for days which are weekends and holidays.
Therefore, event though there might be an event date in Compustat which falls on a holiday, the relevant date in CRSP does not contain any information. So, data is lost when I am trying to merge them.
I have the following files:
data crsp_daily;
input Company_ID $4. Trading_Date;
informat Trading_Date date9.;
format Trading_Date date9.;
cards;
1001 01jan2021
1001 04jan2021
1001 05jan2021
1001 06jan2021
1001 07jan2021
1001 08jan2021
1001 11jan2021
1001 12jan2021
1001 13jan2021
1001 14jan2021
1001 15jan2021
1002 01jan2021
1002 04jan2021
1002 05jan2021
1002 06jan2021
1002 07jan2021
1002 08jan2021
1002 11jan2021
1002 12jan2021
1002 13jan2021
1002 14jan2021
1002 15jan2021
1003 01jan2021
1003 04jan2021
1003 05jan2021
1003 06jan2021
1003 07jan2021
1003 08jan2021
1003 15jan2021
;
run;
data Compustat_date;
input Company_ID $4. Event_date;
informat Event_date date9.;
format Event_date date9.;
cards;
1001 05jan2021
1002 09jan2021
1003 09jan2021
;
run;
I want merge the dates on the following conditions:
1. If an Event_date in Compustat_date file is not present in CRSP_daily file, I want to shift the date in the merged file in the next earliest available Trading_Date. Please see Company_ID 1002 on 09Jan2021 merged on 11Jan2021.
2. However, if the next earliest available trading day is more than 5 Trading_Date later from the Event_date, then I do not want to merge the dates. Rather I want to create a separate file containing that Company.
I am expecting the following output files:
1. First merged file:
| Company_ID | Trading_Date | Event_Date |
| 1001 | 2021-01-01 | |
| 1001 | 2021-01-04 | |
| 1001 | 2021-01-05 | 2021-01-05 |
| 1001 | 2021-01-06 | |
| 1001 | 2021-01-07 | |
| 1001 | 2021-01-08 | |
| 1001 | 2021-01-11 | |
| 1001 | 2021-01-12 | |
| 1001 | 2021-01-13 | |
| 1001 | 2021-01-14 | |
| 1001 | 2021-01-15 | |
| 1002 | 2021-01-01 | |
| 1002 | 2021-01-04 | |
| 1002 | 2021-01-05 | |
| 1002 | 2021-01-06 | |
| 1002 | 2021-01-07 | |
| 1002 | 2021-01-08 | |
| 1002 | 2021-01-11 | 2021-01-11 |
| 1002 | 2021-01-12 | |
| 1002 | 2021-01-13 | |
| 1002 | 2021-01-14 | |
| 1002 | 2021-01-15 | |
| 1003 | 2021-01-01 | |
| 1003 | 2021-01-04 | |
| 1003 | 2021-01-05 | |
| 1003 | 2021-01-06 | |
| 1003 | 2021-01-07 | |
| 1003 | 2021-01-08 | |
| 1003 | 2021-01-15 | |
Second output file containing company that is not merged:
| Company_ID | Event_Date |
| 1003 | 2021-01-09 |
Thank you in advance for your kind support!
