Hi, Can someone help me with this one? If i have two set of data and one (the subset) is contained in the other (the bigger set). How can I delete all the observations which are in the smaller table from the bigger table, using the smaller table? For example:
Set 1 (big set)
X Y
1 2
3 4
5 6
7 8
9 10
Set 2 (subset)
X Y
1 2
5 6
7 8
I want to have as the result:
X Y
3 4
9 10
Thank you very much!
SQL except operator provides the most straightforward approach:
data big;
input x y;
cards;
1 2
3 4
5 6
7 8
9 10
;
data subset;
input X Y;
cards;
1 2
5 6
7 8
;
proc sql;
create table want as
select * from big
except
select * from subset;
quit;
proc print;run;
Haikuo
One way to go:
proc sql;
delete from Set_1
where exists (select * from set_2 where set_1.x=set_2.x and set_1.y=set_2.Y)
;
quit;
SQL except operator provides the most straightforward approach:
data big;
input x y;
cards;
1 2
3 4
5 6
7 8
9 10
;
data subset;
input X Y;
cards;
1 2
5 6
7 8
;
proc sql;
create table want as
select * from big
except
select * from subset;
quit;
proc print;run;
Haikuo
Thank you so much for your help.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.