Retain only records (IDs) in common between two data sets

NewUsr 0
Viewed 22

is there a way to remove from a data set the IDs (that are also replicated) not present in a second data set?

dataset1

ID     Age

00001 34-50
00001 34-50
00002 30-50
00002 30-50
00002 30-50
00005 25-34
00005 25-34
00006 45-50
.... ....

dataset2

ID     Sex

00001 F
00001 F
00002 F
00002 F
00002 F
00004 M
00004 M
00003 F
.... ....

Desired output

ID     Sex

00001 F
00001 F
00002 F
00002 F
00002 F
.... ....

I tried (without success):
>      proc sql;
>      select*from dataset1
>      where ID in(select ID from dataset2),
>      quit;
sas
1 Answers

Try this

data one;
input ID $ Age $;
datalines;
00001 34-50
00001 34-50
00002 30-50
00002 30-50
00002 30-50
00005 25-34
00005 25-34
00006 45-50
;

data two;
input ID $ Sex $;
datalines;
00001 F
00001 F
00002 F
00002 F
00002 F
00004 M
00004 M
00003 F
;

proc sql;
   create table want as
   select two.*
   from two
   where two.ID in (select ID from one)
   ;
quit;
PeterClemmensen0
Related