topic Re: Filtering out all client information based on a large list of client in SAS Programming

Filtering out all client information based on a large list of client

themanwith2thum — Wed, 18 Aug 2021 19:32:28 GMT

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!

Re: Filtering out all client information based on a large list of client

Reeza — Wed, 18 Aug 2021 19:41:00 GMT

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.

Re: Filtering out all client information based on a large list of client

themanwith2thum — Wed, 18 Aug 2021 19:43:59 GMT

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)

Re: Filtering out all client information based on a large list of client

Reeza — Wed, 18 Aug 2021 19:56:53 GMT

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.

Re: Filtering out all client information based on a large list of client

Kurt_Bremser — Wed, 18 Aug 2021 20:10:19 GMT

Use a hash object:

data want;
set a;
if _n_ = 1
then do;
  declare hash b (dataset:"b");
  b.definekey("id");
  b.definedone();
end;
if b.find() = 0;
run;