topic How do I match IDs from multiple dataset to one dataset in SAS Programming

How do I match IDs from multiple dataset to one dataset

danwarags — Thu, 25 Aug 2016 21:39:31 GMT

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:

Subset dataset

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

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

mohamed_zaki — Thu, 25 Aug 2016 22:35:33 GMT

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;

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

PGStats — Thu, 25 Aug 2016 22:37:11 GMT

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;

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

ChrisNZ — Fri, 26 Aug 2016 04:09:25 GMT

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;