How to delete one table (which is a subset of the other table) from the other table

Solved
Frequent Contributor
Posts: 75

How to delete one table (which is a subset of the other table) from the other table

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!

Accepted Solutions
Solution
‎11-04-2012 08:35 AM
Posts: 3,167

Re: How to delete one table (which is a subset of the other table) from the other table

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

All Replies
Posts: 4,736

Re: How to delete one table (which is a subset of the other table) from the other table

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;

Solution
‎11-04-2012 08:35 AM
Posts: 3,167

Re: How to delete one table (which is a subset of the other table) from the other table

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

Frequent Contributor
Posts: 75

Re: How to delete one table (which is a subset of the other table) from the other table

Thank you so much for your help.

🔒 This topic is solved and locked.