topic How to merge the data from Compustat (Quarterly) and CRSP (monthly) in SAS Programming

How to merge the data from Compustat (Quarterly) and CRSP (monthly)

fongdo — Fri, 03 May 2019 00:14:11 GMT

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

Re: How to merge the data from Compustat (Quarterly) and CRSP (monthly)

ChrisNZ — Fri, 03 May 2019 04:51:49 GMT

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

Re: How to merge the data from Compustat (Quarterly) and CRSP (monthly)

fongdo — Sun, 05 May 2019 07:00:09 GMT

Thank you very much @ChrisNZ . It works well

Re: How to merge the data from Compustat (Quarterly) and CRSP (monthly)

jap6198 — Wed, 10 Mar 2021 14:33:34 GMT

How did you merge year and months together?

Re: How to merge the data from Compustat (Quarterly) and CRSP (monthly)

jap6198 — Wed, 10 Mar 2021 17:48:56 GMT

@ChrisNZ

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.