topic Re: how to get rid of middle initial in SAS Enterprise Guide

how to get rid of middle initial

bhca60 — Wed, 20 Jul 2022 23:34:44 GMT

I have a field that has full name including middle initial and possibly full middle name. How do i remove that from the field?

proc sql;
create table one as 
select 
distinct strip(a.name) as name_strip,
dob
from two a
inner join three b
order by name_strip;
quit;

Re: how to get rid of middle initial

SASKiwi — Thu, 21 Jul 2022 00:34:53 GMT

What does your data look like? You need to provide some example data otherwise we will just be guessing. What about multi-word surnames? If you have any, trying to figure out if a word is indeed a middle name or part of a surname is tricky.

Re: how to get rid of middle initial

ballardw — Thu, 21 Jul 2022 00:36:09 GMT

I might try something along the lines of:

case 
   when countw(a.name)<3 then strip(a.name)
   when countw(a.name)=3 then catx(' ',scan(a.name,1),scan(a.name,3))
   when countw(a.name)=4 then catx(' ',scan(a.name,1),scan(a.name,3),scan(a.name,4))
end as name_strip

The count for 3 and 4 a because of names that are compounds like "Le Blanc" "Van something" or such.

If you have some of the long names from some enthic groups that may not be enough.

If you have not seen Countw before it counts words, which are just about anything separated by spaces, commas, periods and couple of other characters. Scan uses the same delimiter characters to grab the numbered "word". Catx combines the results with the characters of the first parameter separating the bits. Each of the bits would have any leading or trailing blanks removed before inserting that parameter.

Re: how to get rid of middle initial

andreas_lds — Thu, 21 Jul 2022 05:01:51 GMT

I just want to put an emphasis on the question asked by @SASKiwi:

What about multi-word surnames? If you have any, trying to figure out if a word is indeed a middle name or part of a surname is tricky.

So please post some data and show the expected result.

Re: how to get rid of middle initial

bhca60 — Thu, 21 Jul 2022 05:36:08 GMT

ex: smith, John R

I'm trying to match names from a database to a file i have with the names in it as well to see what matches i can get from the database

Re: how to get rid of middle initial

SASKiwi — Thu, 21 Jul 2022 20:06:05 GMT

If surnames are always first and followed by a comma then finding name components is relatively easy:

data want;
  name = 'smith, John R';
  surname = scan(name, 1, ',');
  first_name = scan(substr(name, find(name, ',') +1), 1);
  second_name = scan(substr(name, find(name, ',') +1), 2);
  name_new = catx(' ', first_name, surname); 
  put _all_;
run;