turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How to delete one table (which is a subset of the ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-04-2012 06:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to thdang

11-04-2012 08:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to thdang

11-04-2012 08:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to thdang

11-04-2012 08:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Haikuo

11-04-2012 10:10 AM

Thank you so much for your help.