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

[renjithr]

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?

[kiranv_]

proc sql;

select * from member_table

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

quit

[renjithr]

Thanks Kiranv!

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

[ChrisNZ]

Like this?

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

[renjithr]

Hi ChrisNZ,

Sorry, I am afraid it is not the solution.

[kiranv_]

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'));

[ChrisNZ]

Why not? The output is

Member_ID
102
103

[renjithr]

Apologies!

Your solution works, Thank you so much !

[Reeza · Accepted solution]

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;

[renjithr]

Thanks Reeza!