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 :
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;
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.