topic Re: Separate Full Name to FirstName and LastName in SAS Enterprise Guide

Separate Full Name to FirstName and LastName

jsphnwllms — Mon, 30 Apr 2018 14:35:38 GMT

How do I split full name into First Name and Last Name?

Data:

Full Name

George W. Bush

George W Bush

George Bush

Desired Output:

FirstName LastName

George W. Bush

George W Bush

George Bush

(Note: I need the FirstName to include the Middle Initial if it exists.)

If I use:
,scan(fullname,1,' ') AS first
,scan(fullname,2,' ') AS second
,scan(fullname,-1,' ') AS last

I get:

First Second Last

George W. Bush

George W Bush

George Bush Bush

Thanks so much for your help!

Re: Separate Full Name to FirstName and LastName

novinosrin — Mon, 30 Apr 2018 14:43:18 GMT

data have;
input FullName $30.;
datalines;
George W. Bush
George W Bush
George Bush
;

data want;
set have;
call scan(fullname, -1, position, length);
firstname=substr(fullname,1,position-1);
last=scan(fullname,-1,' ') ;
keep firstname last fullname;
run;

Re: Separate Full Name to FirstName and LastName

Astounding — Mon, 30 Apr 2018 14:44:10 GMT

While you may have obscure problems with names like Vincent Van Gogh, your basic idea for first and last name are correct. You can check whether there are 3 names, and only assign the middle name if there is one. The DATA step syntax is easy:

if countw(fullname) > 2 then second = scan(fullname, 2, ' ');

The SQL syntax is harder, and just a little beyond my SQL knowledge. I believe you would need a CASE statement that checks the results of the COUNTW function, and applies the SCAN function if there are 3 names, but returns a blank if there are fewer than 3 names.

Re: Separate Full Name to FirstName and LastName

jsphnwllms — Mon, 30 Apr 2018 15:17:40 GMT

Worked perfectly! I greatly appreciate your help!

Re: Separate Full Name to FirstName and LastName

Reeza — Mon, 30 Apr 2018 15:23:00 GMT

@jsphnwllms please mark the appropriate solution as correct (not this response). Thanks!

Re: Separate Full Name to FirstName and LastName

jsphnwllms — Mon, 30 Apr 2018 20:08:55 GMT

I used the code below and it worked perfectly!

data want;
set have;
call scan(fullname, -1, position, length);
firstname=substr(fullname,1,position-1);
last=scan(fullname,-1,' ') ;
keep firstname last fullname;
run;

Thanks!

Re: Separate Full Name to FirstName and LastName

novinosrin — Mon, 30 Apr 2018 20:28:24 GMT

@jsphnwllms lol Thank you, I hope you learned something from the community while we the community offer the same for all.

Tip: Test these solutions intuitively and you will be able to think through how stuff works. All the best!

Re: Separate Full Name to FirstName and LastName

jsphnwllms — Tue, 01 May 2018 13:15:49 GMT

I have a follow up question. If the full name has a Jr or III, I need to include Jr and III in the last name.

Data:

Full Name

George W. Bush

George W Bush III

George Bush Jr

Desired Output:

FirstName LastName

George W. Bush

George W Bush III

George Bush Jr

(Note: I need the FirstName to include the Middle Initial if it exists and the LastName with III or Jr included.)

Thanks!