BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
renjithr
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
kiranv_
Rhodochrosite | Level 12

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
Quartz | Level 8

Thanks Kiranv!

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

ChrisNZ
Tourmaline | Level 20

Like this?

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

renjithr
Quartz | Level 8

Hi ChrisNZ,

Sorry, I am afraid it is not the solution.

kiranv_
Rhodochrosite | Level 12

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

renjithr
Quartz | Level 8

Apologies!

Your solution works, Thank you so much !

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 9 replies
  • 9571 views
  • 1 like
  • 4 in conversation