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!
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
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
Linlin,
Your answer rocks! Thanks:)
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
Thanks, Haikuo!
The second method will take me some time to understand, but the first one looks really neat:)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.