BookmarkSubscribeRSS Feed
Maisha_Huq
Quartz | Level 8

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!

1 REPLY 1
jklaverstijn
Rhodochrosite | Level 12

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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 749 views
  • 0 likes
  • 2 in conversation