DATA Step, Macro, Functions and more

PROC SQL : How to exclude records based on the values of a column in another table?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

PROC SQL : How to exclude records based on the values of a column in another table?

I have a table that contains  member id and then I wanted to join to another table using member id and look for the member spending code. A member can have multiple spending code, so what I want to achieve is to remove the member from the fianl result if the member has a spending code 'A1'.

 

Using PROC SQL , how can the below results be achieved?

 

 

Here is the example:

 

Member table:

Member_ID

101

102

103

 

Spending table

Member_ID Spending_code

101              B1

101              CC

101              A1

102              CC

102              DD

103             DD

 

My desired result is:

 

Member_id

102

103

 

Could you please help?


Accepted Solutions
Solution
‎06-30-2017 11:26 AM
Super User
Posts: 19,791

Re: PROC SQL : How to exclude records based on the values of a column in another table?

Will every member have data in both tables? 

 

Proc sql;
Create table want as
Select distinct Id 
From table1
Where id not in (select distinct Id from spendingTable where code eq 'A1');
Quit;

View solution in original post


All Replies
PROC Star
Posts: 325

Re: PROC SQL : How to exclude records based on the values of a column in another table?

proc sql;

select * from member_table

where member_id not in(select Member_ID from spending_table where member_id is not null);

quit

Frequent Contributor
Posts: 122

Re: PROC SQL : How to exclude records based on the values of a column in another table?

[ Edited ]

Thanks Kiranv!

But you query is only returning 103. I need both 102 and 103.

PROC Star
Posts: 1,760

Re: PROC SQL : How to exclude records based on the values of a column in another table?

Like this?

 select * from HAVE1
 except
 select unique Member_ID from HAVE2 where Spending_code='A1';

Frequent Contributor
Posts: 122

Re: PROC SQL : How to exclude records based on the values of a column in another table?

Hi ChrisNZ,

Sorry, I am afraid it is not the solution.

PROC Star
Posts: 325

Re: PROC SQL : How to exclude records based on the values of a column in another table?

[ Edited ]

may be something like this

proc sql;
select member_id from membertable where member_id in
(select member_id from spendingtable where member_id not in
(select Member_ID from spendingtable  where spending_code ='A1'));

PROC Star
Posts: 1,760

Re: PROC SQL : How to exclude records based on the values of a column in another table?

Why not? The output is

 

Member_ID
102
103
Frequent Contributor
Posts: 122

Re: PROC SQL : How to exclude records based on the values of a column in another table?

Apologies!

Your solution works, Thank you so much !

Solution
‎06-30-2017 11:26 AM
Super User
Posts: 19,791

Re: PROC SQL : How to exclude records based on the values of a column in another table?

Will every member have data in both tables? 

 

Proc sql;
Create table want as
Select distinct Id 
From table1
Where id not in (select distinct Id from spendingTable where code eq 'A1');
Quit;
Frequent Contributor
Posts: 122

Re: PROC SQL : How to exclude records based on the values of a column in another table?

Thanks Reeza!

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 364 views
  • 1 like
  • 4 in conversation