BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hjjijkkl
Pyrite | Level 9

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                     

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

3 REPLIES 3
andreas_lds
Jade | Level 19

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.

Spoiler
data want;
   set one two;
   by ID;
   
   if not (first.id and last.id);
run;
Ksharp
Super User
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;

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 966 views
  • 1 like
  • 4 in conversation