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?
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;
proc sql;
select * from member_table
where member_id not in(select Member_ID from spending_table where member_id is not null);
quit
Thanks Kiranv!
But you query is only returning 103. I need both 102 and 103.
Like this?
select * from HAVE1
except
select unique Member_ID from HAVE2 where Spending_code='A1';
Hi ChrisNZ,
Sorry, I am afraid it is not the solution.
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'));
Apologies!
Your solution works, Thank you so much !
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;
Thanks Reeza!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.