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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: