DATA Step, Macro, Functions and more

Merging two datasets with two mutually exclusive different key identifiers

Reply
Senior User
Posts: 1

Merging two datasets with two mutually exclusive different key identifiers

Dear all, 

 

I am using sas 9.4 and trying to merge a M&A dataset (DATASET B) with a 60,000 observation with a compustat dataset (DATASET A) with over 800,000 observation. The B dataset has 2 unique identifiers. for the US firm it has CUSIP and for non-us firm it has sedol,  and year that I can use for merge. on the other hand, the A dataset has both the identifiers and the year. I was wondering if there is any way that I can merge the two datasets (in a way that dataset A is a left join that keeps all the variables) based on identifier 1 OR identifier 2. meaning use identifier 1 for merge if the B has identifier 1 OR use identifier 2 if the B has identifier 1.

 This is what I originally used and It absolutely did not work since half of the data has cusip and the other has sedol. Perhaps the data step be a better choice for these type of merging.

proc sql;

create table mna5 as select unique *

from comp as a left join mna as b

on a.cusip=b.cusip and a.sedol=b.sedol and a.year=b.year;

quit;

 

Thanks

Super User
Posts: 13,898

Re: Merging two datasets with two mutually exclusive different key identifiers

A couple of short examples of the data that demonstrate the problem would be helpful. A data step to create an example using the variables of interest and the cases that you have and the final desired result would be optimal.

 

I would be tempted to create a set for matches on cuspid, another from matches on sedol, combine the results and remove duplicates as a crude easy to understand approach.

Then look for the ones that had no matches.

 

 

Super User
Posts: 6,908

Re: Merging two datasets with two mutually exclusive different key identifiers

Would this work?

 

on (a.cusip=b.cusip or a.sedol=b.sedol) and a.year=b.year;

 

Ask a Question
Discussion stats
  • 2 replies
  • 130 views
  • 0 likes
  • 3 in conversation