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`).<\/p>\n
Please note this program uses the macro `ICLINK`. In order to use this macro, you\u00a0need to add the following line to your autoexec.sas file in your WRDS home directory (see here<\/a> for details):<\/p>\n `options sasautos<\/span>=(‘\/wrds\/wrdsmacros\/’<\/span>, SASAUTOS) MAUTOSOURCE<\/span>;`<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading proc sql;\r\n\/* Compustat: COMPANY Dataset Vs. NAMES Dataset\r\nhttps:\/\/wrds-web.wharton.upenn.edu\/wrds\/support\/Additional%20Support\/WRDS%20Knowledge%20Base%20with%20FAQs.cfm?folder_id=658&article_id=2837 *\/\r\n create table aa1\r\n as select a.*, b.gvkey, b.fic, b.sic\r\n from audit.auditnonreli a left join comp.company b\r\n on not missing(a.company_fkey) and a.company_fkey=b.cik;\r\n\r\n create table aa2 as\r\n select a.*, b.lpermno as permno\r\n from aa1 a left join crsp.ccmxpf_lnkhist b\r\n on not missing(a.gvkey) and a.gvkey=b.gvkey \r\n and b.LINKPRIM in ('P', 'C')\r\n and b.LINKTYPE in ('LU', 'LC')\r\n\t and not missing(file_date)\r\n and (b.LINKDT<=a.file_date or missing(b.LINKDT))\r\n and (a.file_date<=b.LINKENDDT or missing(b.LINKENDDT));\r\n\r\n\/* Merging IBES with Compustat Cusip\r\nThe main problem of linking Compustat with IBES is not the fact that Compustat's cusip is 9\r\ncharacter, whereas IBES is 8-character. The main issue is that Compustat Cusip is header\r\n(most recent), whereas IBES Cusip is hsitorical (as of date).\r\n\r\nWRDS has a few research applications intended to demonstrate possible approaches that can\r\nbe used in order to merge Compustat GVKEY to IBES ticker. First, Compustat provides a linking\r\nheader table between GVKEY and IBES ticker (IBTIC) in its SECURITY table\r\n(located in \/wrds\/comp\/sasdata\/na\/security\/).\r\n\r\nIf you want a more comprehensive map between GVKEY and IBES Ticker, check out on of the recent\r\nresearch applications on WRDS (P\/E Ratio), which demonstrates how to obtain a linking table\r\nbetween GVKEY and IBES Ticker using CRSP-Compustat Merged product as well as WRDS ICLINK product).*\/\r\n create table aa3\r\n as select a.*, b.ibtic\r\n from aa2 a left join\r\n (select distinct gvkey, ibtic from comp.security\r\n where not missing(gvkey) and not missing(ibtic) and iid='01') b\r\n on a.gvkey=b.gvkey\r\n order by a.res_notify_key;\r\nquit;\r\n\r\n%ICLINK (IBESID=IBES.ID,CRSPID=CRSP.STOCKNAMES,OUTSET=ICLINK);\r\n\r\ndata noticker; set aa3;\r\n where not missing(permno) and missing(ibtic);\r\n drop ibtic;\r\nrun;\r\n\r\nproc sort data=iclink (where=(score in (0,1,2))) out=ibeslink;\r\n by permno ticker score;\r\nrun;\r\n\r\ndata ibeslink; set ibeslink;\r\n by permno ticker; if first.permno;\r\nrun;\r\n\r\nproc sql;\r\n create table noticker1\r\n as select a.*, b.ticker as ibtic\r\n from noticker a left join ibeslink b\r\n on a.permno=b.permno\r\n order by res_notify_key;\r\nquit;\r\n \r\ndata aa;\r\n set aa3 (where=(missing(permno) or not missing(ibtic))) noticker1;\r\n label ibtic='IBES Ticker';\r\nrun;<\/pre>\n