Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

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
Respected Advisor
Posts: 3,124

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,886

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
Respected Advisor
Posts: 3,124

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 203 views
  • 0 likes
  • 3 in conversation