topic Re: Filter data based on first, middle and last names in SAS Programming

Filter data based on first, middle and last names

buddha_d — Wed, 27 Apr 2022 20:44:00 GMT

proc sql;
create table WORK.have( bufsize=65536 )
  (
   acct_nbr char(150) format=$150. informat=$150. label='acct_number',
   Acct_NAME char(150),
   cust_name1 char(150),
   cust_name2 char(150),
   first_name char(150),
   middle_name char(150),
   last_name char(150)
  );

insert into WORK.have
values('54321', 'BUTSCH FAITH', 'PETER R BUTSCH', '', 'PETER', 'RAY', 'BUTSCH' )
values('54321', 'BUTSCH PETER', 'FAITH F BUTSCH', '', 'FAITH', 'FINN', 'BUTSCH' )
values('54321', 'FAITH BUTSCH', 'PETER R BUTSCH', '', 'PETER', 'RIVER', 'BUTSCH' )
values('54321', 'FINN BUTSCH', 'PETER R BUTSCH', '', 'PETER', 'RIVER', 'BUTSCH' )
values('54321', 'BUTSCH RAY', 'PETER R BUTSCH', '', 'PETER', 'RIVER', 'BUTSCH' )
values('54321', 'RAY PETER', 'PETER R BUTSCH', '', 'PETER', 'RIVER', 'BUTSCH' )
values('54321', 'MARY JAY RICHARDSON', 'FAITH F BUTSCH', '', 'FAITH', 'FINN', 'BUTSCH' )
values('12345', 'DONALD W WICK', 'NANCY DIANE WICK','', 'NANCY', 'DIANE','WICK')
values('12345', 'DONALD WICK', 'NANCY DIANE WICK','', 'NANCY', 'DIANE', 'WICK')
values('12345', 'NANCY WICK',	'DONALD W WICK','','DONALD','W','WICK')
;
quit;

I have the above data. This is the sample data and I need to filter data based on acct_name field with first_name, middle_name and last_name fields. You could use other fields as well. But, what I need to see is the record where acct_name is totally different from any first_name, middle_name and last_name.

The output should look like :

acct_nbr Acct_NAME cust_name1 cust_name2 first_name middle_name last_name
54321 MARY JAY RICHARDSON FAITH F BUTSCH FAITH FINN BUTSCH

Re: Filter data based on first, middle and last names

japelin — Thu, 28 Apr 2022 01:00:50 GMT

data want;
  set have;
  array names{5} cust_name1 cust_name2 first_name middle_name last_name;
  do i=1 to dim(names);
    index=find(Acct_NAME,names{i},'IT');
    if index>0 then delete;
  end;
  drop i;
run;

Re: Filter data based on first, middle and last names

s_lassen — Thu, 28 Apr 2022 04:57:21 GMT

You could try something like this:

proc sql;
  create table want as select * from have
  where not indexw(Acct_NAME,first_name) 
    and not indexw(Acct_NAME,middle_name) 
    and not indexw(Acct_NAME,last_name) ;

The solution is similar to the datastep shown by @japelin , but it does not check CUST_NAME1-2, as you did not mention those, and it checks if the first/middle/last name occurs as a word in ACCT_NAME (assuming that you want records like 'WALTER ROBINSON' vs. 'ROBIN', 'T', 'HENDRICKS', - ROBIN should not match ROBINSON) by using the INDEXW function.

Re: Filter data based on first, middle and last names

buddha_d — Thu, 28 Apr 2022 16:19:35 GMT

Japelin, your code works for me. Thanks for sharing the logic.

Re: Filter data based on first, middle and last names

buddha_d — Thu, 28 Apr 2022 16:20:22 GMT

S_lassen, your logic works for me as well. Thank you so much for sharing.