SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file.

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file.

[ Edited ]

I am having a problem in merging two datasets--COMPUSTAT annual and CRSP Monthly Stock file. 

Example:

COMPUSTAT DATA:

gvkey   datadate      yr    indfmt  consol   popsrc   datafmt  tic        cusip
001000 31dec1974 1974 INDL  C                D         STD       AE.2  000032102
001004 31may1975 1974 INDL C                D          STD       AIR    000361105
001005 31oct1974 1974 INDL   C                D          STD       ABA.2 000370106
001006 30jun1974 1974 INDL C                  D          STD       1145B 000736108
001007 30sep1974 1974 INDL C                 D          STD        4135B 000774109

 

CRSP DATA:

gvkey   linkprim linktype lpermno lpermco iid datadate tic cusip
001000 P LU 25881 23369 01 31mar1974 AE.2 000032102
001000 P LU 25881 23369 01 28feb1974 AE.2 000032102
001000 P LU 25881 23369 01 31jan1974 AE.2 000032102
001004 P LU 54594 20000 01 31mar1974 AIR 000361105

 

the CRSP datasets is monthly whereas COMPUSTAT is annual. CRSP also has a YEAR variable.

 

Thank you for your step by step reply.

 


Accepted Solutions
Solution
‎09-04-2016 09:27 PM
Super Contributor
Posts: 266

Re: Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file.

data CRSP_new;
set CRSP;
yr = YEAR(date);
run;

proc sql;
create table Mereged_table as
select a.* , b.*
from COMPUSTAT a
inner join CRSP_new b
on a.gvkey = b.gvkey and a.yr= b.yr;
quit;

Hope I got your problem right.

Step1. in CRSP dataset, create new variable yr that would hold value of year.

 

Step2. Merge COMPUSTAT and CRSP_new on two columns gvkey and yr.

 

Above code it is not tested.

View solution in original post


All Replies
Solution
‎09-04-2016 09:27 PM
Super Contributor
Posts: 266

Re: Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file.

data CRSP_new;
set CRSP;
yr = YEAR(date);
run;

proc sql;
create table Mereged_table as
select a.* , b.*
from COMPUSTAT a
inner join CRSP_new b
on a.gvkey = b.gvkey and a.yr= b.yr;
quit;

Hope I got your problem right.

Step1. in CRSP dataset, create new variable yr that would hold value of year.

 

Step2. Merge COMPUSTAT and CRSP_new on two columns gvkey and yr.

 

Above code it is not tested.

Super Contributor
Posts: 266

Re: Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file.

It would be great if you list what should your output look like.

Super User
Posts: 19,822

Re: Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file.


RahulG wrote:
data CRSP_new;
set CRSP;
yr = YEAR(date);
run;

proc sql;
create table Mereged_table as
select a.* , b.*
from COMPUSTAT a
inner join CRSP_new b
on a.gvkey = b.gvkey and a.yr= b.yr;
quit;

Hope I got your problem right.

Step1. in CRSP dataset, create new variable yr that would hold value of year.

 

Step2. Merge COMPUSTAT and CRSP_new on two columns gvkey and yr.

 

Above code it is not tested.


@RahulG In this case it might be easier to do the calculation in the join condition?

 

a.yr=year(b.date)

 

Contributor
Posts: 20

Re: Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file.

Thank you. It worked. 

Contributor
Posts: 20

Re: Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file.

Thank you.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1499 views
  • 4 likes
  • 3 in conversation