Separate Full Name to FirstName and LastName

[jsphnwllms]

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!

[novinosrin]

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;

[Astounding]

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.

[jsphnwllms]

Worked perfectly! I greatly appreciate your help!

[Reeza]

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

[jsphnwllms]

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! 

[novinosrin]

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

[jsphnwllms]

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!