BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Right
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

4 REPLIES 4
Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

Linlin,

Your answer rocks! Thanks:)

Haikuo
Onyx | Level 15

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
Calcite | Level 5

Thanks, Haikuo!

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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