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

[nzrdufiu]

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.

[RahulG · Accepted solution]

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]

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

[Reeza]

---

@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)

[nzrdufiu]

Thank you. It worked. 

[nzrdufiu]

Thank you.

[Kyojik]

How can you get GVKEY in CRSP ?

[Darrel]

Hi, I am new on the forum but I am doing my MBA thesis on executive compensation and need to download data from ExecuComp. Can anyone help with acess to this database?

[Reeza]

I believe these data are here, but you need to have access via your institution as well. 

https://wrds-web.wharton.upenn.edu/wrds/

[Kyojik]

It is not allowed to distribute these databases. It will be a problem for Author if he/she is using some data which is not subscribed by their institutions. The most likely way is to collaborate (joint research) with someone from the institution who has access. 

[Darrel]

Thank you for your reply. However this is exactly my problem my university does not have a WRDS account

[Darrel]

Is there a way someone can help me get access?

[Reeza]

They have a trial access, did you try that approach? Not sure if that would let you take data out. And how can you site the source if you don't have access?

I think this is a problem you should discuss with your thesis supervisor.

---

@Darrel wrote:
Is there a way someone can help me get access?

---