BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sas_noob25
Obsidian | Level 7

I have a problem I'm trying to solve, and not really sure where to start. 

 

I have two tables Table A and Table B. I'm trying to delete ALL of the customer IDs from Table A if they appear in Table B, but Table A sometimes has multiple entries for the same customer ID. My end result should be Table A with only records that are not in Table B.

 

Example:

Table A

Customer ID Date
A5/7/2020
A5/11/2020
B5/8/2020
B5/9/2020
B5/10/2020
C5/11/2020

 

Table B

Customer IDDelete Flag
BY
BY
AY

 

In this example I would only want Customer ID C to show up in my final table.

 

I have a few thousand rows of data in Table A that I'm checking against. Any help is greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
svh
Lapis Lazuli | Level 10 svh
Lapis Lazuli | Level 10

This is relatively straightforward with a subquery like this:

 

(I have altered the name of the variable "Customer ID". It can work as a named literal like this: 'Customer ID'n

 

proc sql;
   create table want as
   select * from A
  where customer_ID not in (select customer_ID from B);
quit;

View solution in original post

5 REPLIES 5
svh
Lapis Lazuli | Level 10 svh
Lapis Lazuli | Level 10

This is relatively straightforward with a subquery like this:

 

(I have altered the name of the variable "Customer ID". It can work as a named literal like this: 'Customer ID'n

 

proc sql;
   create table want as
   select * from A
  where customer_ID not in (select customer_ID from B);
quit;
Sas_noob25
Obsidian | Level 7

Thank you! Super easy way to do this, not sure how I didn't think of this.

Kurt_Bremser
Super User

If data size and performance is an issue, use a hash object:

data want;
set a;
if _n_ 01
then do;
  declare hash b (dataset:"b");
  b.definekey("customer_id");
  b.definedone();
end;
if b.check() ne 0;
run;
Reeza
Super User

proc sort data=tableB out=b_unique nodupkey;
by customerID;
run;

proc sort data=tableA;
by customerID;

data tableC;
merge tableA(in=ina) b_unique(in=inb drop=deleteflag);
by customerID;
if not inb;
run;

A SQL way that will delete records from the table - test thoroughly before using something like this:

data tableA;
infile cards dlm='09'x;
input CustomerID  $	Date : ddmmyy10.;
format date date9.;
cards;
A	5/7/2020
A	5/11/2020
B	5/8/2020
B	5/9/2020
B	5/10/2020
C	5/11/2020
;;;;
run;

data tableB;
infile cards dlm='09'x;
input CustomerID $	DeleteFlag $;
cards;
B	Y
B	Y
A	Y
;;;;
run;

proc sql;
delete * from tableA where customerID  in (select distinct(CustomerID) from TableB);
quit;
Sas_noob25
Obsidian | Level 7

Thank you!

 

This step below worked as well.

 

proc sort data=tableB out=b_unique nodupkey;
by customerID;
run;

proc sort data=tableA;
by customerID;

data tableC;
merge tableA(in=ina) b_unique(in=inb drop=deleteflag);
by customerID;
if not inb;
run;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 8104 views
  • 2 likes
  • 4 in conversation