topic SAS SQL data deletion problem in SAS Procedures

SAS SQL data deletion problem

Right — Fri, 03 Feb 2012 02:27:33 GMT

Hi All,

I am a newbie and have this question in using SAS/SQL:

I have two Tables A and B, and they have a common column named ID. Table B has a single column with distinct elements. The ID column inTable A have some repeated elements, some of which also appear in Table B.

I want to delete all rows in Table A where the elements in the ID column appear in the ID column of Table B.

Any suggestions? SAS/DATA method will also be helpful. Thanks!

SAS SQL data deletion problem

Linlin — Fri, 03 Feb 2012 02:57:21 GMT

how about:

data tableA;

input id age;

cards;

1 20

1 30

2 20

3 30

4 50

;

data tableB;

input id;

cards;

;

proc sql;

create table want as

select * from TableA

where tableA.id not in (select id from tableB);

quit;

proc print;run;

Obs id age

1 2 20

2 4 50

Linlin

SAS SQL data deletion problem

Right — Fri, 03 Feb 2012 03:05:03 GMT

Linlin,

Your answer rocks! Thanks:)

SAS SQL data deletion problem

Haikuo — Fri, 03 Feb 2012 03:37:16 GMT

I know you asked for a SQL solution and Linlin has offered a great answer. Just FYI, If using Datastep, there are many approaches as well:

1. Simple merge

data want;

merge a(in=a) b(in=b);

by id;

if a and not b ;

run;

2. Hash, not very efficient in this case, but you don't need to presort the data.

data want (drop=rc);

if 0 then set b;

declare hash h(dataset:"work.b");

h.defineKey('id');

h.defineData('id');

h.defineDone();

set a;

rc=h.find();

if rc ne 0 then output;

run;

Regards,

Haikuo

SAS SQL data deletion problem

Right — Sat, 04 Feb 2012 00:56:44 GMT

Thanks, Haikuo!

The second method will take me some time to understand, but the first one looks really neat:)