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

[thdang]

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!

[Patrick]

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;

[Haikuo · Accepted solution]

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

[thdang]

Thank you so much for your help.