The following program is used to link each financial restatement in Audit Analytics to Compustat, CRSP, and I/B/E/S. The resultant dataset aa contains unique identifiers of Audit Analytics (res_notify_key), Compustat (gvkey), CRSP (permno), and I/B/E/S (ibtic).
Please note this program uses the macro ICLINK. In order to use this macro, you need to add the following line to your autoexec.sas file in your WRDS home directory (see here for details):
options sasautos=('/wrds/wrdsmacros/', SASAUTOS) MAUTOSOURCE;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
proc sql; /* Compustat: COMPANY Dataset Vs. NAMES Dataset https://wrds-web.wharton.upenn.edu/wrds/support/Additional%20Support/WRDS%20Knowledge%20Base%20with%20FAQs.cfm?folder_id=658&article_id=2837 */ create table aa1 as select a.*, b.gvkey, b.fic, b.sic from audit.auditnonreli a left join comp.company b on not missing(a.company_fkey) and a.company_fkey=b.cik; create table aa2 as select a.*, b.lpermno as permno from aa1 a left join crsp.ccmxpf_lnkhist b on not missing(a.gvkey) and a.gvkey=b.gvkey and b.LINKPRIM in ('P', 'C') and b.LINKTYPE in ('LU', 'LC') and not missing(file_date) and (b.LINKDT<=a.file_date or missing(b.LINKDT)) and (a.file_date<=b.LINKENDDT or missing(b.LINKENDDT)); /* Merging IBES with Compustat Cusip The main problem of linking Compustat with IBES is not the fact that Compustat's cusip is 9 character, whereas IBES is 8-character. The main issue is that Compustat Cusip is header (most recent), whereas IBES Cusip is hsitorical (as of date). WRDS has a few research applications intended to demonstrate possible approaches that can be used in order to merge Compustat GVKEY to IBES ticker. First, Compustat provides a linking header table between GVKEY and IBES ticker (IBTIC) in its SECURITY table (located in /wrds/comp/sasdata/na/security/). If you want a more comprehensive map between GVKEY and IBES Ticker, check out on of the recent research applications on WRDS (P/E Ratio), which demonstrates how to obtain a linking table between GVKEY and IBES Ticker using CRSP-Compustat Merged product as well as WRDS ICLINK product).*/ create table aa3 as select a.*, b.ibtic from aa2 a left join (select distinct gvkey, ibtic from comp.security where not missing(gvkey) and not missing(ibtic) and iid='01') b on a.gvkey=b.gvkey order by a.res_notify_key; quit; %ICLINK (IBESID=IBES.ID,CRSPID=CRSP.STOCKNAMES,OUTSET=ICLINK); data noticker; set aa3; where not missing(permno) and missing(ibtic); drop ibtic; run; proc sort data=iclink (where=(score in (0,1,2))) out=ibeslink; by permno ticker score; run; data ibeslink; set ibeslink; by permno ticker; if first.permno; run; proc sql; create table noticker1 as select a.*, b.ticker as ibtic from noticker a left join ibeslink b on a.permno=b.permno order by res_notify_key; quit; data aa; set aa3 (where=(missing(permno) or not missing(ibtic))) noticker1; label ibtic='IBES Ticker'; run; |
Hi Kai,
Your posts are super helpful and I really enjoyed reading them.
For this post, I have got one question:
Could we access the autoexec.sas file in home directory via PC SAS Connect? Or has to use SSH to access and change autoexec.sas file?
Thanks!
We have to use SSH to access the file. I usually use Cyberduck, a FTP-like app on my Mac to access and edit this file. If you are familiar with Linux-like command line, you can simply access and edit this file via Terminal (or anything equivalent on PC). Also ensure you are SSHing the right server (wrds-cloud.wharton.upenn.edu) since WRDS is transitioning to its new Cloud server recently.
Hi Kai,
Thank you for your material. It helps me a lot!
I am wondering how to identify the year the restated financial statements were originally issued?
Thank you in advance!
By definition, this may be not a one-to-one match. A restatement disclosure may affect financial statements in several prior years. For example, when a sales recognition error is identified in a restatement disclosure, the sales in several prior years may be required to restate. AA collects restatement disclosure. Each restatement disclosure may restate multiple financial statements. I do not have a good idea now and sorry I cannot give you a more positive reply.
Hi Kai,
Thank you for your reply! I will look up more materials.
So the following paragraph in your code is to merge all the restatements related to a specific firm, right? It is a m:1 match, right?
proc sql;
/* Compustat: COMPANY Dataset Vs. NAMES Dataset
https://wrds-web.wharton.upenn.edu/wrds/support/Additional%20Support/WRDS%20Knowledge%20Base%20with%20FAQs.cfm?folder_id=658&article_id=2837 */
create table aa1
as select a.*, b.gvkey, b.fic, b.sic
from audit.auditnonreli a left join comp.company b
on not missing(a.company_fkey) and a.company_fkey=b.cik;
This paragraph is to link a restating firm to its GVKEY via the common identifier in both datasets. It is a 1:1 match.
Oh, I see, thank you for your reply!
Hi Kai,
Correct me if I am wrong. The following code will delete the duplicate observations. But why you only keep the first.permno? Shouldn’t we keep the ones with highest scores? Further, when two duplicate observations have the same score, why we should keep the first.permno?
Thanks!
data ibeslink; set ibeslink;
by permno ticker; if first.permno;
run;
Sorted already in the previous PROC step. Give me a few examples of tie scores.
for example permno 49322 link to IBES ticker ARB and ARLI, both score are zero.
Dear Chen,
Thank you very much for the code.
Could you please post a code for linking between compustat and audit analytics without CRSP and I/B/E/S?
Thank you very much again,
Dear Kai,
Many thanks for the codes and they really help a lot!
Can I ask a dumb question about how to find “the linking header table between GVKEY and IBES ticker (IBTIC) in its SECURITY table (located in /wrds/comp/sasdata/na/security/)”.
Is this Security table still available on WRDS? I could not find this table in my WRDS account. Do you have an Internet link for this table?
Thank you very much!!
I use FileZilla to download data, and now you can find SECURITY table under this route: /wrdslin/comp/sasdata/naa/security/security.sas7bdat
Dear Kai,
Thanks deeply for your post.
I want to ask a question on the mapping between AuditAnalytics and Compustat. It looks like the comp.company only keeps one CIK record for each gvkey, so I guess it’s the header CIK. But I think the CIK in AA is historical. Do you think there would be some observations lost if we simply match these two sets by the CIK code?
Thanks very much!
Dear Prof.Chen,
Thank you for posting and sharing your code!
I want to ask two question regarding the merge between the Compstat and IBES dataset. My aim is to match the earning forecast and the actual earnings. However, there will be two issues. The first one is that firm has different share classes and IBES also include the forecast of different securities of a firm. I wonder is there any way to adjust that? Another question is regarding the shares outstanding. I remembered that you mentioned in another blog that we should use the shares outstanding in Compustat. But I wonder in this case, should we use the CRSP shares outstanding since it will bypass the stock split if we use the unadjust file of IBES? And if we do so, how can we deal with the difference between the different shares outstanding since the earning-per-share data is based on the shares outstanding from Compustat.
Thank you for your help in advance!
Best regards,
Roger