how to get rid of middle initial

[bhca60]

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;

[SASKiwi]

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.

[bhca60]

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

[SASKiwi]

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;

[ballardw]

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.

[andreas_lds]

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.