BookmarkSubscribeRSS Feed
themanwith2thum
Calcite | Level 5

Hello I have one table (Table A) which has info about our clients. Their names, ID's, products owned, address etc etc. Most clients will have multiple entries since they own multiple products. I need to extract information for 50 or so clients from that list, these required clients name are present in a different table (Table B). If I try using an if statement that would take forever to write down all 50 clients name plus leaves room for a lot of error. I was wondering if there is a faster way to do this. I considered doing a left join but not sure if this would work since Table A would have multiple entries under the clients name and the name repeated many times while Table B would not have any repeats. 

 

Thanks in advance!

4 REPLIES 4
Reeza
Super User
proc sql;
create table want as
select *
from tableA
where clientname in (Select client_name from tableB);
quit;

What do you want to do about the repeats? A left join will get you ALL the repeats, if you had multiples in each this actually wouldn't work. You can then use a data step to reduce this using first/last but there are typically other rules you can use to filter - ie pick most recent updated.
themanwith2thum
Calcite | Level 5

Hi Thanks for the quick reply. I want to keep the repeats. I want all the information for client from Table B (The one with multiple entries of client name) based on which clients are mentioned in Table A (The table with single entries of client names)

Reeza
Super User
Then either a left/right join (depending on how you specify the tables) a data step merge with IN or the solution I initially posted works.
Please try them and let us know if you have any issues.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4 replies
  • 361 views
  • 2 likes
  • 3 in conversation