Desktop productivity for business analysts and programmers

Separate Full Name to FirstName and LastName

Reply
Occasional Contributor
Posts: 11

Separate Full Name to FirstName and LastName

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!

Super User
Posts: 2,061

Re: Separate Full Name to FirstName and LastName

Posted in reply to jsphnwllms
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;
Super User
Posts: 6,933

Re: Separate Full Name to FirstName and LastName

Posted in reply to jsphnwllms

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.

Occasional Contributor
Posts: 11

Re: Separate Full Name to FirstName and LastName

Posted in reply to Astounding

Worked perfectly! I greatly appreciate your help!

Super User
Posts: 24,004

Re: Separate Full Name to FirstName and LastName

Posted in reply to jsphnwllms

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

Occasional Contributor
Posts: 11

Re: Separate Full Name to FirstName and LastName

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! 

Super User
Posts: 2,061

Re: Separate Full Name to FirstName and LastName

Posted in reply to jsphnwllms

@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!

Occasional Contributor
Posts: 11

Re: Separate Full Name to FirstName and LastName

Posted in reply to novinosrin

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!

Ask a Question
Discussion stats
  • 7 replies
  • 359 views
  • 2 likes
  • 4 in conversation