DATA Step, Macro, Functions and more

How do I match IDs from multiple dataset to one dataset

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

How do I match IDs from multiple dataset to one dataset

Hello Everyone,

 

I have two datasets. One dataset is the subset of another dataset. I want to match both of the IDs . I want to find IDs from the subset dataset that wont match with the master dataset. For example:

 

Master dataset:

 

ID
1
2
3
4
5

 

 

Subset dataset

 

 

ID
2
4
5
9

 

When we match both datasets, My result should look like this:

 

9

Accepted Solutions
Solution
‎08-26-2016 02:48 AM
Respected Advisor
Posts: 4,920

Re: How do I match IDs from multiple dataset to one dataset

Posted in reply to danwarags

Use the EXCEPT operator in SQL:

 

proc sql;
select id from Subset
except
select id from Master;
quit;

or the NOT IN operator

 

proc sql;
select id from Subset
where id not in (select id from Master);
quit;
PG

View solution in original post


All Replies
Super Contributor
Posts: 490

Re: How do I match IDs from multiple dataset to one dataset

Posted in reply to danwarags
data a;
input ID;
datalines;
1
2
3
4
5
;
 
data b;
input ID;
datalines;
2
4
5
9
;



proc sql;
 create table want as
 select ID
 from b
 where ID not in (select ID from a);
quit;
Solution
‎08-26-2016 02:48 AM
Respected Advisor
Posts: 4,920

Re: How do I match IDs from multiple dataset to one dataset

Posted in reply to danwarags

Use the EXCEPT operator in SQL:

 

proc sql;
select id from Subset
except
select id from Master;
quit;

or the NOT IN operator

 

proc sql;
select id from Subset
where id not in (select id from Master);
quit;
PG
PROC Star
Posts: 1,759

Re: How do I match IDs from multiple dataset to one dataset

Posted in reply to danwarags

A data step will be the fastest since the data is sorted.

 

data WANT;

  merge MASTER(in=M) SUBSET(in=S);

  by ID;

  if S and not M;

run;

 

if you have duplicates and want to avoid them, the if clause becomes :

  if first.ID and S and not M;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 247 views
  • 5 likes
  • 4 in conversation