Linking CRSP and Compustat

Drafted by Rui Dai in Jun 2016 (Revised at Oct 2017)

In empirical finance and accounting literature, CRSP and Compustat have been the two most widely used database since early 90’s. From 2001 to 2015, the combined web queries of those two databases together occupy more than 80% web traffic at WRDS. In this WRDS Research Application, we provide a general guidance to link those two databases at security level. This rest of this application is organized as following:

Preliminary: security identifiers
Data Structure: CRSP v.s. Compustat
Matching Implementation:
- Linking by CUSIP
- Linking by CCM Linktable

1. Preliminary: Security Identifiers

Among all commonly used security/company identifiers, TICKER and CUSIP are probably the most often mentioned ones. A stock TICKER is an abbreviation used to uniquely identify publicly traded shares of a particular stock, while CUSIP is a nine-character alphanumeric code assigned by CUSIP bureau to identify various securities. There are a few problems associated with those two identifiers for researchers to match historical records. First of all, neither identifier is permanent. A change in a security’s CUSIP, for example, could be triggered by any change in the security, including non-fundamental events such as splits and name changes. In addition, retired stock TICKER are often reused. As shown in Table below, both TICKER and CUSIP are changed 3 times, while the name of the company is also changed once.

For those reasons, most databases create permanent identifiers to indicate the same securities and companies. For example, CRSP employs PERMNO to track stock, while Compustat uses GVKEY to follow companies and a combination of GVKEY and IID to track security. As indicated in Table above, both PERMNO and GVKEY (+IID) remain the same, regardless of changes in TICKER, CUSIP, and Company Names.

2. Data Structure:

To understand the matching mechanism and results, researchers should keep the structural differences of two databases in mind. The Table below demonstrates four major structural differences between CRSP and Compustat:

Those differences typically create three kinds of unmatchable scenarios.

Prior to 1950, trading records (CRSP) cannot be matched to fundamental items (Compustat).
The fundamental items cannot be matched to trading records, when firms are listed in regional exchanges, are traded as OTC, and/or only have bonds publicly traded.
The trading records cannot be matched to fundamental items, when those stocks did not survive long enough (e.g. M&A) for Compustat to include its fundamental items.

3. Matching Implementation:

Merge by CUSIP

CUSIP is a nine-character alphanumeric identifier for publicly traded securities, and is created by CUSIP Service Bureau in 1968.Unlike TICKER, CUSIP is not a reusable security identifier, so that it has been widely used to match different databases. CRSP and Compustat used slightly different approach to keep CUSIP records. CRSP drops the 9^th checksum digit in CUSIP and keeps historical CUSIPs, while Compustat use full CUSIP and keep only the most current CUSIP.[3] The following is a SAS sample code to match CRSP and Compustat at security level by CUSIP:

proc sql;

create table cusip_link as

select distinct a.permno, intnx('month',date,0,'e') as date format=date9.,

round(abs(prc), 0.01) as prc, c.gvkey, c.iid

from crsp.msf as a inner join crsp.msenames(where=(shrcd in (10 11 12 30 31 32))) as b

/*Keep All Possible Common Stocks and ARDs*/

on a.permno=b.permno

inner join comp.security as c

on substr(c.cusip,1,8)=substr(b.ncusip,1,8) and lengthn(strip(ncusip))>6

inner join (

select distinct gvkey, datadate from comp.funda

where sale>. or at>. /*Keep company with valid Total Asset or Sales Figure*/

) as d

on d.gvkey=c.gvkey and

intnx('month', datadate, -24, 'e')<a.date<=intnx('month', datadate, 12, 'e');

/*Keep valid CUSIP linked PERMNO and GVKEY pairs around fiscal year ends*/

quit;

There are three major limitations to match CRSP and Compustat by CUSIP:

Prior to 1968, CUSIP does not exist, so that the CUSIP-based match is only reliable from 1968 onward.
CRSP only keep historical CUSIPs when the securities are traded in major exchanges. If the most recent CUSIP in Compustat is from regional or OTC market, CRSP and Compustat records would not be matched properly.
CRSP and Compustat has different data refreshing frequency. The most recent CUSIP in one databases may not be synchronized with the most recent CUSIP in the other, which may in term creates certain miss matchings as well.

Merge by CCM Linktable

The CRSP/Compustat Merged Database (CCM) is comprised of CRSP and Compustat data together with the link and link-history references between these two databases. The key product of CCM is merely a permanent identifier linking table (PERMNO to GVKEY+IID), though CCM is often wrongly considered as merged product of CRSP stock market data with Compustat accounting data. From an end user’s perspective, CCM only adds a link of PERMNO and PERMCO to Compustat database, so that Compustat items can be searched by CRSP's identifiers. In the link table (Ccmxpf_linktable), there are a few indicators for the quality of matching:

LinkType: A code describing the connection between the CRSP and Compustat data.

LU: Link research complete. Standard connection between databases.
LC: Un-researched link to issue by CUSIP.
LD: Duplicate link to a security. Another GVKEY/IID is a better link to that CRSP record
…

LinkPrim: Primary issue indicator for the link

P: Primary, identified by Compustat in monthly security data.
C: Primary, assigned by CRSP to resolve ranges of overlapping or missing primary markers from Compustat in order to produce one primary security throughout the company history.
...

UsedFlag: Flag marking whether link is used in building composite record

1: this link is applicable to the selected PERMNO and used to identify ranges of Compustat data from a GVKEY used to build a composite GVKEY record corresponding to the PERMNO.
-1 = this link is informational, indirectly related to the PERMNO, but not used.
USEDFLAG is a legacy indicator which is kept for programing and data consistency at WRDS.

LINKDT: The first effective date of the link

LINKENDDT: The last date when the link is valid. For current valid link, LINKENDDT is set a NULL value (.E in SAS Format).

The following is a sample SAS code to match CRSP and Compustat at security level by CCM Linktable:

proc sql;

create table ccm_link as

select distinct a.*, c.gvkey, liid as iid

from crsp.msf as a inner join crsp.msenames(where=(shrcd in (10 11 12 30 31 32))) as b

on a.permno=b.permno

inner join crsp.Ccmxpf_linktable(where=(linktype in ('LC' 'LU') and

linkprim in ('P' 'C' 'J') and USEDFLAG=1)) as c

/*Both Primary and Secondary Shares*/

on a.permno=c.lpermno and linkdt<=date<=coalesce(linkenddt, today())

inner join (

select distinct gvkey from comp.funda

where sale>. or at>.

) as d

on c.gvkey=d.gvkey;

quit;

Numbers of companies from stock level matching by CCM Linktable and CUSIP through years are plotted in graph below. Due to various limitations of CUSIP matching mentioned above, CCM always provides greater numbers of matched results, and the two results converge in most recent years. In addition, I provides a code (PCCM.sas below) that generates a combination of CUSIP match and successive price matching. As mentioned above, this approach is inspirited by the CCM LinkType variable. The code is in Alpha version stage, certain cautions are required when use. The numbers of companies matched by using the linking table generated by using this code is also reported in the figure below. As shown in the picture, the price matching does improve the quality of security matching, though CCM still provide the best results, especially in late 80's and early 90's.

It is worth to notice one company may have multiple classes of common equities. And at company level, CRSP and Compustat occasionally disagree on the surviving company from merges. In these cases, a researcher could disagree with the stand taken by the CCM set in resolving the disagreement or ambiguity about a surviving company.

[1] In fact, CRSP includes stocks listed in different exchanges through stages. In particular, CRSP adds stocks listed NYSE, NYSE MKT (AMEX), NASDAQ, and ARCA in 1925, 1962, 1972, and 2006 respectively.

[2] Early researches, for example Kothari, Shanken and Sloan (1995), find a selection bias (survivorship bias) to the way in which firms are added to the COMPUSTAT database. In particular, Compustat adds a new firm into its system only when the firm has reported its financial statement for multiple years, which is sometimes called backfilling bias.

[3] Compustat also provides historical CUSIPs, TICKERs, etc. in its Snapshot product, though historical identifiers are not available in their standard fundamental data feed.

/******************** PCCM.SAS ********************/

/********************Author: Rui Dai********************/

proc sql;

create table Secm (where=(prccm>0)) as

select distinct a.gvkey, a.iid, intnx('month',datadate,0,'e') as datadate format=date9.,

round(prccm, 0.01) as prccm

from comp.Secm(where=(lengthn(strip(iid))=2))/*USA Firm Only*/ as a

inner join (

select distinct gvkey from comp.funda

where sale>. or at>. /*Firm with Sales or Total Asset only*/

) as b

on a.gvkey=b.gvkey;

quit;

/*CRSP Monthly Security File Cleansing*/

proc sql;

create table msf(where=(prc>0)) as

select distinct a.permno, intnx('month',date,0,'e') as date format=date9.,

round(abs(prc), 0.01) as prc

from crsp.msf as a inner join crsp.msenames(where=(shrcd in (10 11 12 30 31 32))) as b

on a.permno=b.permno;

quit;

/*CUSIP Matching*/

proc sql;

create table cusip_match as

select distinct a.gvkey, a.iid, b.permno

from comp.security as a inner join crsp.msenames as b

on substr(a.cusip,1,8)=substr(b.ncusip,1,8)

inner join (select distinct permno from msf) as c

on b.permno=c.permno

inner join (select distinct gvkey, iid from secm) as d

on a.gvkey=d.gvkey and a.iid=d.iid;

quit;

/*A Blind Price Matching*/

proc sql;

create table _pccm_ as

select distinct a.gvkey, permno, a.iid

from Secm as a inner join msf as b

on datadate=date and prc>0.5 and prc=prccm /*Removing very long price matching*/

group by b.permno, a.gvkey, a.iid

having count(distinct datadate)> 7 ; /*Parameter 2*/

quit;

/*Refine matching with time dimension*/

proc sql;

create table meta as

select distinct a.permno, intnx('month',date,0,'e') as DATE format=date9.,

b.gvkey, b.iid, prc, prccm, prc>0.5 and prc=prccm as sdum /*Removing very long price matching*/

from msf as a inner join _pccm_ as b

on a.permno=b.permno

inner join Secm as c

on b.gvkey=c.gvkey and b.iid=c.iid and datadate=date

order by permno, gvkey, iid, date;

quit;

/*Counting backwards to fine-tuning the matched point*/

PROC EXPAND data=meta OUT=meta METHOD=NONE;

by permno gvkey iid;

CONVERT sdum = MS_sdum / TRANSFORM=(MOVSUM 15); /*Parameter 2*/

id date;

RUN;

/* Counting forwards for fine-tuning the matched point (Three steps:)*/

proc sort data=meta out=meta; by permno gvkey iid descending date; run;

data meta; set meta; by permno gvkey iid descending date;

idx+1;

if first.iid then idx=1;

run;

PROC EXPAND data=meta OUT=meta METHOD=NONE;

by permno gvkey iid;

CONVERT MS_sdum = MS_mdum / TRANSFORM=(MOVmax 15); /*Parameter 2*/

id idx;

RUN;

/*Keep consequent matched price and date points*/

proc sql undo_policy=none;

create table meta as

select distinct *, sum(sdum) as hcnt "historical matched count"

from meta(drop=idx)

where MS_mdum>=1 and MS_sdum>=1

group by permno, gvkey, iid

having max(ms_sdum)> 7 /*Parameter 2*/

order by permno, gvkey, iid, date;

quit;

/*Among the remained matched ones, count the closest spelling distance*/

proc sql;

create table mname_match as

select distinct a.gvkey, a.iid, a.permno, spedis(upcase(compress(COMNAM,,'sp')), upcase(compress(conm,,'sp'))) as spedis

from (select distinct gvkey, iid, permno from meta) as a

left join crsp.msenames as b

on a.permno=b.permno

left join comp.company as c

on a.gvkey=c.gvkey

group by a.permno, a.gvkey, a.iid

having spedis(COMNAM, conm)=max(spedis(COMNAM, conm));

quit;

/*Among the remained matched ones, check whether same tickers are evered shared*/

proc sql;

create table tick_match as

select distinct a.gvkey, a.iid, a.permno, scan(upcase(b.ticker),1) = scan(upcase(c.tic),1) as tmatched

from (select distinct gvkey, iid, permno from meta) as a

left join crsp.msenames(where=(lengthn(TICKER)>0)) as b

on a.permno=b.permno

left join comp.Security(where=(lengthn(tic)>0)) as c

on a.gvkey=c.gvkey and a.iid=c.iid

group by a.permno, a.gvkey, a.iid

having (scan(upcase(b.ticker),1) = scan(upcase(c.tic),1))

=max(scan(upcase(b.ticker),1) = scan(upcase(c.tic),1));

quit;

/*Matched name and ticker measures back to consequent matching results and

construct key monthly statistics*/

proc sql;

create table _pccm_ as

select distinct a.*, max(hcnt) as mhcnt, max(MS_sdum) as mms_sdum, max(ms_mdum) as mms_mdum,

count(distinct catt(a.gvkey, a.iid)) as gcnt, b.permno>. as cusip_matched, spedis,

max(b.permno>.) as mcusip_matched, tmatched, max(tmatched) as mtmatched

from meta as a left join cusip_match as b

on a.permno=b.permno and a.gvkey=b.gvkey and a.iid=b.iid

left join mname_match as c

on a.permno=c.permno and a.gvkey=c.gvkey and a.iid=c.iid

left join Tick_match as d

on a.permno=d.permno and a.gvkey=d.gvkey and a.iid=d.iid

group by a.permno, date

order by a.permno, date, a.gvkey, a.iid;

quit;

/*Cleansing procedure:

Among multiple matches, keep the ones with either cusip matched or ticker matched

For the remainig ones, some random statistics are used to remove wrong matched results*/

data _pccm_; set _pccm_;

if gcnt>1 and mcusip_matched=1 and cusip_matched^=1 then delete; /*Keep CUSIP Matched Only*/

else if gcnt>1 and mtmatched=1 and tmatched^=1 then delete; /*Keep CUSIP Matched Only*/

if mcusip_matched=0 and mtmatched=0 and spedis>10 and MS_mdum<=3 and hcnt<5 then delete;/*Keep the ones with similar names but matched only a few times - Value decided by observations*/

else if mcusip_matched=0 and mtmatched=0 and spedis>30 and hcnt<15 then delete;/*Keep the ones with different names but with a lot matches - Value decided by observations*/

drop mhcnt mms_sdum mms_mdum gcnt mcusip_matched mtmatched;

run;

/*To have the best one-to-one (permno to gvkey iid) match*/

proc sql;

create table pccm as

select distinct *

from (

select distinct *

from (

select distinct *

from (

select distinct *

from _pccm_

group by permno, date

having hcnt=max(hcnt) /*1) among the current best, get the best historical best*/

)

group by permno, date

having MS_sdum=max(MS_sdum) /*2) at given day, get the best matches*/

)

group by gvkey, iid, date

having hcnt=max(hcnt)

)

group by gvkey, iid, date

having MS_sdum=max(MS_sdum) ;

quit;

/*Patching based on observations */

proc sql undo_policy=none;

create table pccm as

select distinct *

from (

select distinct *

from pccm

group by permno, gvkey, date

having iid=min(iid)

)

group by permno, date

having spedis=min(spedis)

order by permno, date;

quit;

/*Add Time dimensions to PERMNO_to_IID through CUSIP*/

proc sql;

create table cusip_match_valid as

select distinct a.permno, a.date, b.gvkey, b.iid

from msf as a inner join Cusip_match as b

on a.permno=b.permno

inner join (

select distinct gvkey, datadate from comp.funda

where sale>. or at>.

) as c

on b.gvkey=c.gvkey and

intnx('month', datadate, -24, 'e')<a.date<=intnx('month', datadate, 12, 'e')

left join pccm as d

on a.permno=d.permno and b.gvkey=d.gvkey and b.iid=d.iid and a.date=d.date

group by a.permno, a.date

having (d.permno>.)=max(d.permno>.) and count(distinct catt(gvkey,iid))=1;

quit;

/*Combine CUSIP and PRICE match*/

proc sql;

create table _link_table_ as

select distinct a.permno, a.date, b.gvkey, b.iid,

c.gvkey as cusip_gvkey, c.iid as cusip_iid

from msf as a left join pccm as b

on a.permno=b.permno and a.date=b.date

left join cusip_match_valid as c

on a.permno=c.permno and a.date=c.date

order by permno, gvkey, iid, date;

quit;

/*Making a range (two steps)*/

data _link_table_(where=(lengthn(gvkey)>0)); set _link_table_;

if gvkey="" and iid="" then do;

gvkey=cusip_gvkey; iid=cusip_iid;

end;

drop cusip_gvkey cusip_iid;

proc sort; by permno gvkey iid date;

run;

data _link_table_; set _link_table_; by permno gvkey iid date;

retain from;

ldate=lag(date);

if first.iid then do; ldate=.; from=date; end;

else if intnx('month',ldate, 1, 'e')<intnx('month',date, 0, 'e') then

from=date;

format from date9.;

run;

/*Save the matched Table*/

proc sql;

create table pccm_table as

select distinct permno, gvkey, iid, from, date as to format=date9.

from _link_table_

group by permno, gvkey, iid, from

having date=max(date);

quit;

Google Sites

Report abuse