thank you for your answer. I have to use stata for my thesis, is there also a linking table available for stata, or only for SAS? Can I somehow download the linking table in an stata format?

In general (if it is not, or would not work), this is a way to get files downloaded without using SAS (with SAS, use rsubmit with proc download):

1. Use WRDS’ website to find out the location of the CCM linktable (support -> dataset list -> crsp), locations look like “/wrds/crsp/sasdata/a_ccm”
2. Use SSH-FTP to download the dataset
3. Use Stat Transfer to convert to Stata format https://www.stattransfer.com/

Hey,
I also want to merge international, so non-US, firms from Compustat Global with analyst data from IBES but I am not able to do it.
I tried to use the CCM linking table, but then I am left with Canadian firms only, so this is no opportunity.
I tried to use the ISIN and SEDOL from Compustat to obtain the IBES CUSIP but that does not work either.

Is there a way to combine the two databases for international (also not cross-listed) firms? If yes, how can I do that?
By the way, I am also using Stata.

I don’t have much experience matching Compustat Global and IBES so I posted your question on the Google group. I will post any replies here. Feel free to post your findings here as this is a challenging match and useful for others.

I believe that the trick to match IBES to Compustat Global is to understand how they report SEDOL/CUSIP. Compustat Global uses 7-digit SEDOLs: 6-digit SEDOL plus the check digit. IBES reports 8-digit SEDOL under the variable CUSIP: the first two digits are for the country and the last 6 are the 6-digit SEDOL. To match the two datasets we need to use the first 6 digits in Compustat SEDOL and the last 6 digits of in IBES CUSIP.

Here is a Stata code, assuming that you want to match Compustat with analysts’ forecasts 4 months after the report:

use ibes, clear keep if usfirm==0 gen sedol = substr(cusip,3,6) gen month=mofd(statpers) /* if "statpers" is correctly recorded as a date variable */ save 1, replace

use compustat_global, clear gen month = mofd(datadate) + 4 drop if sedol=="" replace sedol = substr(sedol,1,6) replace sedol = "0" + sedol if length(sedol)==5 replace sedol = "00" + sedol if length(sedol)==4 keep sedol month duplicates drop merge 1:m sedol month using 1, keep(match)

Hi Joost,
Thank you for this post. I was wondering if modifying the second line of the above code will validly apply the merge for US firms as well. Intuitively, I can delete the “keep if usfirm==0” to capture US firms as well, or replace it with “keep if usfirm==1” to apply the merge to US firms only. Will this be valid in your view?
Many thanks,
Samuel.

Are US firms in Compustat Global? If they are, it could work. How about giving it a try?

Best,

Joost

Hi Joost,

Thank you for your response. I thought so, but just realized they are not. I will see how I may apply the procedure to US firms in Compustat North America.

The second post in this thread has a link to iclink.sas; that script basically creates a linktable between Compustat and IBES (sounds like that is what you need).

I am also trying to merge IBES and Compustat North America Fundamentals. I have done so using CUSIPS (by using the CUSIP converter to convert 8-digit CUSIPS from IBES to 9-digit CUSIPS as in Compustat)).

However, I was wondering whether I have a problem now with regard to the dates. My Compustat data are sorted by fiscal years, while in IBES summary I looked at Forecast Period End Date (which I understand as being the year to which the estimates apply). But I am not sure whether the match that I made is valid with regard to the dates, because of different definitions in Compustat and IBES.

For example:
A fiscal year in Compustat of 1996 can refer to, for instance, 31/3/1997 (according to the definition of fiscal years in Compustat). If that is the case, does the information that I read out of IBES (in my case, number of estimates) in year 1996 (Forecast Period End Date) correspond correctly to fiscal year 1996 from Compustat? I doubt it, but I have no idea how to fix this. I am not familiar with SAS codes or anything, I am trying to merge in Excel.

I agree with Joost; I have done as suggested and it worked fine for me. As fyear makes adjustment for fiscal period end, you will note a few mismatch for firms with fiscal period end from January through May if you match using fyear.