topic Re: Most effective way to merge COMPUSTAT annual and CRSP monthly stock return file. in SAS Data Management

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

nzrdufiu — Mon, 29 Aug 2016 03:06:43 GMT

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.

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

RahulG — Mon, 29 Aug 2016 03:56:03 GMT

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.

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

RahulG — Mon, 29 Aug 2016 03:57:00 GMT

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

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

Reeza — Mon, 29 Aug 2016 06:04:59 GMT

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

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

nzrdufiu — Mon, 05 Sep 2016 01:27:56 GMT

Thank you. It worked.

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

nzrdufiu — Mon, 05 Sep 2016 01:28:32 GMT

Thank you.

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

Kyojik — Thu, 30 Nov 2017 05:34:18 GMT

How can you get GVKEY in CRSP ?

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

Darrel — Tue, 09 Oct 2018 20:50:02 GMT

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?

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

Reeza — Tue, 09 Oct 2018 20:52:18 GMT

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

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

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

Darrel — Tue, 09 Oct 2018 20:57:19 GMT

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

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

Darrel — Tue, 09 Oct 2018 21:05:20 GMT

Is there a way someone can help me get access?

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

Reeza — Tue, 09 Oct 2018 21:12:32 GMT

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?

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

Kyojik — Wed, 10 Oct 2018 05:34:38 GMT

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.