SAS SQL data deletion problem

[Right]

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!

[Linlin · Accepted solution]

how about:

data tableA;

input id age;

cards;

1 20

1 30

2 20

3 30

4 50

;

data tableB;

input id;

cards;

1

3

;

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

[Right]

Linlin,

Your answer rocks! Thanks:)

[Haikuo]

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

[Right]

Thanks, Haikuo!

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