topic How to delete one table (which is a subset of the other table) from the other table in SAS Procedures

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

thdang — Sun, 04 Nov 2012 11:08:56 GMT

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!

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

Patrick — Sun, 04 Nov 2012 13:33:09 GMT

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;

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

Haikuo — Sun, 04 Nov 2012 13:35:18 GMT

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

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

thdang — Sun, 04 Nov 2012 15:10:57 GMT

Thank you so much for your help.