topic how to combine two dataset to read only observations that are in both data in SAS Programming

how to combine two dataset to read only observations that are in both data

hjjijkkl — Wed, 10 Mar 2021 03:51:42 GMT

I have two dataset similar to the examples below, i wanted to read only observations that are in both data.

Dataset one Dataset two
ID   A   B ID   A B
10   1   2 10   0 4
20   3   4 30   5 2
30   5   6 40   7 3

I want the output that looks like this

ID   A   B
10   1   2
10   0 4

30 5 6

30 5 2

Re: how to combine two dataset to read only observations that are in both data

andreas_lds — Wed, 10 Mar 2021 06:28:52 GMT

What have you tried?

You need to interleave both datasets, outputting only those observations for which first.id and last.id are not true / one at the same time.

data want;
   set one two;
   by ID;
   
   if not (first.id and last.id);
run;

Re: how to combine two dataset to read only observations that are in both data

Ksharp — Wed, 10 Mar 2021 11:16:28 GMT

data one;
input ID   A   B   ;
cards; 
10   1   2                          
20   3   4                          
30   5   6                 
;
data two;
input ID   A  B ;
cards; 
 10   0  4
 30   5  2
 40   7  3
 ;

 proc sql;
 create table want as
 select * from one where id in (select id from two)
 union all
 select * from two where id in (select id from one) 
order by id;
 quit;

Re: how to combine two dataset to read only observations that are in both data

Kurt_Bremser — Wed, 10 Mar 2021 17:55:59 GMT

Is it possible that there are two or more observations for a single ID in one dataset?