Help using Base SAS procedures

SAS SQL data deletion problem

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

SAS SQL data deletion problem

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!


Accepted Solutions
Solution
‎02-02-2012 09:57 PM
Super Contributor
Posts: 1,636

SAS SQL data deletion problem

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

View solution in original post


All Replies
Solution
‎02-02-2012 09:57 PM
Super Contributor
Posts: 1,636

SAS SQL data deletion problem

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

Occasional Contributor
Posts: 9

SAS SQL data deletion problem

Linlin,

Your answer rocks! ThanksSmiley Happy

Respected Advisor
Posts: 3,124

SAS SQL data deletion problem

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

Occasional Contributor
Posts: 9

SAS SQL data deletion problem

Thanks, Haikuo!

The second method will take me some time to understand, but the first one looks really neatSmiley Happy

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 140 views
  • 1 like
  • 3 in conversation