How to combine ID variables into one dataset and compare?

[Maisha_Huq]

Hi there! I have three different datasets, where each dataset is essentially one unique ID per client. In a perfect scenario, the datasets were supposed to have been merged on matching unique IDs between the three datasets. The problem is that the datasets' unique IDs for the same unique person weren't assigned to match; they're maybe similar. Or, they're different ID's altogether and a probabilistic match will have to be done by comparing many of the demographic variables for each client. Instead of a probabilistic match, I'd like to bring the three unique ID variables into one dataset and use the compged function to compare the level of "sameness" between each unique ID in dataset1 and every other unique ID in datasets 2 and 3. How would I bring the three unique ID variables into one dataset if I can't merge the three datasets on the IDs? [Version: SAS 9/4.] Thanks so much!

[jklaverstijn]

You can create a table with all combinations of the ID's using SQL. This is called a Cartesian product and is generally flagged as a possible programming error. That's because it is a common mistake that can easily create a run-away query because often the number of combinations gets very large. Assuming you have a workable number of combinations (the product of the number of unique ID values for each of your three ID variables), this is would be the way:

proc sql;
create table all_ids as
select a.id as id_a, b.id as id_b, c.id as id_c
compged(a.id, b.id) as comp_ab, compged(a.id, c.id) as com_ac, compged(b.id, c.id) as comp_bc
from a,b,c;
quit;

You can then work out your best choices for each id by doing some magic on the resulting table.

As the query is a join without join criteria you will get the following note in your log. But in this case it is intentional and you can ignore it:

NOTE: The execution of this query involves performing one or
      more Cartesian product joins that can not be optimized. 

Hope this helps,

- Jan.