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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

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
  • 5 replies
  • 4920 views
  • 2 likes
  • 4 in conversation