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.
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.