Only gvkey variable to match Computstat and CRSP database is possible ?
Posted: 20 April 2016 05:29 PM   [ Ignore ]
Newbie
Rank
Total Posts:  2
Joined  2016-04-20

Hi,

I am constructing a database (using data available in CCM) in which i have quarterly fundamental data from Computstat and daily security data from CRSP.

If I understood the matching method, I have to match the 2 databases thanks to the companies’s gvkey identifier from computstat and the securities’s lpermno identifier from CRSP?
And to do that, I have to download the “linking table” in which I’ll find the gvkey and permno (that are, if I have well understood, unique for each company).

As an alternative method (which seems simpler to me), is it possible to directly match the two databases from the gvkey identifier which occurs in the 2 databases?

Thank you in advance for your answers

Profile
 
 
Posted: 21 April 2016 08:23 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Jeremy,

Yes, you need the linktable, because gvkey is not used by CRSP (other than in the linktable).

There was a change Feb 2014 in the linktable:
“As of the February 2014 release, USEDFLAG is no longer used in the WRDS CCM web queries.  Please select LINKTYPES LC, LU, and LS for the same results.  These represent the vast majority of the links between CRSP securities and Compustat companies, without introducing duplicate data.  The WRDS-created linking dataset (ccmxpf_linktable) has been deprecated.  It will continue to be created for a transition period of 1 year. SAS programmers should use the Link History dataset (ccmxpf_lnkhist) from CRSP.”

I just noticed this recently, and haven’t transitioned yet to the new linktable. (The ‘old’ linktable is still available for download so that is what I am still using; the ‘old’ code to match is:

proc sql
  
create table getf_3 as 
  
select a.*, b.lpermno as permno
  from getf_2 a left join crsp
.ccmxpf_linktable b 
    on a
.gvkey eq b.gvkey 
    
and b.lpermno ne 
    and 
b.linktype in ("LC" "LN" "LU" "LX" "LD" "LS"
    and 
b.linkprim IN ("C""P")  
    and ((
a.datadate >= b.LINKDT) or b.LINKDT eq .B) and  
       ((
a.datadate <= b.LINKENDDT) or b.LINKENDDT eq .E)   ; 
quit

This should give you some idea how the join works.

Hope this helps,

Joost

 

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 21 April 2016 10:22 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  2
Joined  2016-04-20

Thank you for this quick reply.

I am very surprised by your statement that gvkey is not used in CRSP because it is present in my daily database that I got with CCM (CRSP/Compustat Merged Database - Security Daily).

If this is the case, do you think I can use the gvkey variable to match this daily database (in which gvkey variable is present) with my quarterly computstat database?

I’d prefer this solution if it is possible because, not mastering the sql language, i could make the same work with SAS base.

jérémy

Profile
 
 
Posted: 21 April 2016 01:36 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Ok, I understand now. In principle, gvkey is not used by CRSP. However, CRSP sells some datasets where they have merged their data with compustat. The table you are looking at has been merged for you already, and you can indeed match on gvkey on that table.

Best Regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 20 May 2016 04:18 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  3
Joined  2016-05-20

Hi Joost,

I also need some sort of linktable to merge ISS director (formerly risk metrics) data with compustat data but I cannot find this table. Could you explain to me where to find it?

I would like to match the databases on gvkey and fiscal year but for the ISS data I dont have the gvkey variable. Could you help me out?

Thanks in advance.

Best,
Roy

Profile
 
 
Posted: 22 May 2016 08:03 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Roy,

Does this post help? http://www.wrds.us/index.php/forum_wrds/viewthread/597/

Best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 30 May 2016 09:31 AM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  3
Joined  2016-05-20

Hi Joost, I was able to merge the two datasets but now I am stuck with merging CRSP and Compustat file (without gvkey). I need to link the data on permno but I can’t figure out how to code this.
Could you explain this once more? I have already dowloaded the link table.

Thanks in advance.

Regards,

Roy

Profile
 
 
Posted: 30 May 2016 06:23 PM   [ Ignore ]   [ # 7 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Roy,

For each date range (begin and end date) the linktable has the permno and the gvkey. So, if you have a date and a permno you can get gvkey. If you have a date and gvkey, you can get the permno.

Hope this helps,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 31 May 2016 03:19 AM   [ Ignore ]   [ # 8 ]
Newbie
Rank
Total Posts:  3
Joined  2016-05-20

Hi Joost,

I understand. Unfortunately there are no dates in the linktable that I downloaded. Could that be possible. In CRSP there is a date variable indeed.

I have tried to attach the datafile (Stock Returns) for which I need to get GVKEY and the Linktable I downloaded. Unfortunately the files are too large.

Maybe you have some more ideas on how to link the gvkey from the linktable to the Stock Return data.

Thanks in advance.

Best,

Roy Steinvoort

Profile
 
 
Posted: 31 May 2016 09:16 AM   [ Ignore ]   [ # 9 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Roy,

You can use SSH to download SAS datasets, and convert them with Stata Trans into another format if needed.

The directory where the linkhist file is: Path on WRDS Server: /wrds/crsp/sasdata/a_ccm

https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=137&file_id=66977

Notice the date columns; without beginning and end dates I don’t see how you can get a good match.

Hope this helps,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 23 June 2016 10:14 AM   [ Ignore ]   [ # 10 ]
Newbie
Rank
Total Posts:  3
Joined  2016-06-23

hi Joost,

I am totally new to the CCM database. I followed your posts earlier, and I have some questions that I hope you could help me with.
1. when I downloaded the CCM database, I get lpermno and lpermco, also cusip and gvkey
2. download the linking table, which i upload the lpermno txt file that I got from the ccm file earlier

the linking table file does not have any beginning date or ending date for a fiscal year.
and after this, what should i do to merge ccm and crsp daily?

Thank you,
An

Profile
 
 
Posted: 23 June 2016 10:21 AM   [ Ignore ]   [ # 11 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi An,

Maybe I am missing something but if you have a CCM dataset that has permno and gvkey already, why would you need the linktable? (the linktable is used to find a permno for a gvkey, or the other way around—when using Compustat and CRSP seperately).

Best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 24 June 2016 03:46 AM   [ Ignore ]   [ # 12 ]
Newbie
Rank
Total Posts:  3
Joined  2016-06-23

Hi Joost!

First of all, thank you so much for your quick reply. I am sorry that i couldn’t ask you earlier, probably due to the time difference.

Yes, I have downloaded the CCM, and i have Gvkey, lpermno, lpermco, cusip. But still how do i merge ccm with crsp?
In CCM, it is lpermno and in Crsp it is only permno.
And how about the years? since Crsp has daily data and CCM has annual data.

I am having a big trouble with this merging. I never imagine that it could be so complicated like this. I really hope that you could show me how to merge them.

Kind regards,
An

 

Profile
 
 
Posted: 27 June 2016 02:10 PM   [ Ignore ]   [ # 13 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi An,

lpermno and permno are the same variable, and can be used to match. What variable are you trying to construct with the crsp daily data?

Typical software that people use to match and construct variables are Stata and SAS. But SPSS, Excel, R, Python etc would also work.

Best Regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile