Using WRDS to connect RepRisk ESG data with IBES earnings estimates data
About the Linking Method
To match data from RepRisk to IBES we use IBES's IDSUM file.
There are two key IBES identifier files: (1) ID for IBES Details; and (2) IDSUM for IBES summary data (located at
/wrds/ibes/sasdata). These two files provide the primary identifier, the IBES TICKER (named TICKER) as well as the CUSIP and the exchange ticker.
An important difference between the ID and IDSUM files is the coverage and identification of Canadian companies. While the USFIRM dummy variable designates US companies in IDSUM, the detail history ID file contains duplicate records for each Canadian company, with one record for USFIRM= 1 and another record for USFIRM= 0. This is due to IBES's inclusion of the Canadian estimates in both the U.S. source file and the International source file. Therefore, it is easier to identify U.S. companies in the IDSUM set. Also, the variable named CUSIP could correspond to SEDOL when the CUSIP is not available for non-U.S. firms.
This following sample SAS code shows the procedure for matching with RepRisk using the IBES IDSUM file.
proc sql; create table rep_ibes as select unique a.ticker, b.reprisk_id , b.isin, substr(b.isin,3,8) as cusip from ibes.idsum(where=(cusip ne '')) as a, reprisk.v2_wrds_company_lookup as b where a.cusip=substr(b.isin,3,8); quit;