I would like to merge the data from Compustat (Quarterly) and CRSP (monthly) by FimID. I have created COMP_T and CRSP_T by merging year and month. Now I would like to get a new dataset where for each FirmID, every month of COMP_T is merged with next three months of CRSP_T. For example, 197912 of COMP_T is merged with the data of 198001, 198002, and 198003 from CRSP_T. Please see a small sample below: Thank you very much for your help.
HAVE
Compustat
FrimID COMP_DATE COMP_T Asset
1 31/12/1979 197912 10
1 31/03/1980 198003 20
2 30/09/1980 198009 30
CRSP
FrimID CRSP_DATE CRSP_T Return
1 31/12/1979 197912 0.04
1 31/01/1980 198001 0.02
1 29/02/1980 198002 0.03
1 31/03/1980 198003 0.04
1 30/04/1980 198004 0.10
1 31/05/1980 198005 0.09
1 30/06/1980 198006 0.07
2 30/09/1980 198009 0.05
2 31/10/1980 198010 0.06
2 30/11/1980 198011 0.09
2 31/12/1980 198012 0.14
WANT
FrimID COMP_T CRSP_T Asset Return
1 197912 198001 10 0.02
1 197912 198002 10 0.03
1 197912 198003 10 0.04
1 198003 198004 20 0.10
1 198003 198005 20 0.09
1 198003 198006 20 0.07
2 198009 198010 30 0.06
2 198009 198011 30 0.09
2 198009 198012 30 0.14
Like this?
proc sql;
select HAVE.FRIMID, COMP_T, CRSP_T, ASSET, RETURN
from HAVE, CRSP
where HAVE.FRIMID =CRSP.FRIMID and intck('quarter', COMP_DATE, CRSP_DATE) = 1
order by 1,2,3;
quit;
FrimID | COMP_T | CRSP_T | Asset | Return | a |
---|---|---|---|---|---|
1 | 197912 | 198001 | 10 | 0.02 | 1 |
1 | 197912 | 198002 | 10 | 0.03 | 1 |
1 | 197912 | 198003 | 10 | 0.04 | 1 |
1 | 198003 | 198004 | 20 | 0.1 | 1 |
1 | 198003 | 198005 | 20 | 0.09 | 1 |
1 | 198003 | 198006 | 20 | 0.07 | 1 |
2 | 198009 | 198010 | 30 | 0.06 | 1 |
2 | 198009 | 198011 | 30 | 0.09 | 1 |
2 | 198009 | 198012 | 30 | 0.14 | 1 |
Like this?
proc sql;
select HAVE.FRIMID, COMP_T, CRSP_T, ASSET, RETURN
from HAVE, CRSP
where HAVE.FRIMID =CRSP.FRIMID and intck('quarter', COMP_DATE, CRSP_DATE) = 1
order by 1,2,3;
quit;
FrimID | COMP_T | CRSP_T | Asset | Return | a |
---|---|---|---|---|---|
1 | 197912 | 198001 | 10 | 0.02 | 1 |
1 | 197912 | 198002 | 10 | 0.03 | 1 |
1 | 197912 | 198003 | 10 | 0.04 | 1 |
1 | 198003 | 198004 | 20 | 0.1 | 1 |
1 | 198003 | 198005 | 20 | 0.09 | 1 |
1 | 198003 | 198006 | 20 | 0.07 | 1 |
2 | 198009 | 198010 | 30 | 0.06 | 1 |
2 | 198009 | 198011 | 30 | 0.09 | 1 |
2 | 198009 | 198012 | 30 | 0.14 | 1 |
Thank you very much @ChrisNZ . It works well
This did not work for me. I am trying to do the same thing; however, when I type this in SAS, January-March do not show up. It just starts at April. Please let me know if I am doing something wrong.
How did you merge year and months together?
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 25. 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.