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

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

renjithr — Thu, 29 Jun 2017 22:50:42 GMT

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?

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

kiranv_ — Thu, 29 Jun 2017 22:54:17 GMT

proc sql;

select * from member_table

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

quit

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

renjithr — Thu, 29 Jun 2017 23:00:28 GMT

Thanks Kiranv!

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

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

ChrisNZ — Thu, 29 Jun 2017 23:20:00 GMT

Like this?

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

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

renjithr — Thu, 29 Jun 2017 23:23:30 GMT

Hi ChrisNZ,

Sorry, I am afraid it is not the solution.

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

kiranv_ — Thu, 29 Jun 2017 23:27:47 GMT

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

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

Reeza — Thu, 29 Jun 2017 23:41:23 GMT

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;

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

ChrisNZ — Fri, 30 Jun 2017 00:09:20 GMT

Why not? The output is

Member_ID
102
103

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

renjithr — Fri, 30 Jun 2017 15:31:00 GMT

Apologies!

Your solution works, Thank you so much !

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

renjithr — Fri, 30 Jun 2017 15:31:17 GMT

Thanks Reeza!