I have a table called table1. I want to subset table2 into two different tables based on if the values in table1 match table2
Example
table1
ID
2
3
5
table2
ID
1
2
3
4
5
6
I want to split table2 into table3 and table4
If table2 has an ID that matches table1 then move that row into table3
If table2 does not match any IDs in table1 put those rows in table4
table3
ID
2
3
5
table4
ID
1
4
6
Is this possible with a merge in one data step?
Thank you for the help
If your tables are already sorted by id you could use something like:
data table1;
input ID;
cards;
2
3
5
8
;
data table2;
input ID;
cards;
1
2
3
4
5
6
;
data table3 table4;
merge table1 (keep=id in=one) table2 (in=two);
by id;
if one and two then output table3;
else if two then output table4;
run;
This can be accomplished quite simple with a data step MERGE using IN= data set options, combined with conditional OUTPUT.
If your tables are already sorted by id you could use something like:
data table1;
input ID;
cards;
2
3
5
8
;
data table2;
input ID;
cards;
1
2
3
4
5
6
;
data table3 table4;
merge table1 (keep=id in=one) table2 (in=two);
by id;
if one and two then output table3;
else if two then output table4;
run;
Art,
thank you for the help that did exactly what I wanted. Thank you to everyone for your help.
Like this:
data table1;
input ID;
cards;
2
3
5
;
data table2;
input ID;
cards;
1
2
3
4
5
6
;
data want1 want2;
merge table2 (in=t2) table1(in=t1);
by id;
if t1 and t2 then output want1;
if t2 and not t1 then output want2;
run;
proc print;run;
Please note, you probably want to only keep the variables form table2 by using the data step options: data want1 (keep=) want2 (keep=);
Edit: Art just has offered a great solution.
Regards,
Haikuo
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.