BookmarkSubscribeRSS Feed
jsphnwllms
Fluorite | Level 6

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!

7 REPLIES 7
novinosrin
Tourmaline | Level 20
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
PROC Star

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
Fluorite | Level 6

Worked perfectly! I greatly appreciate your help!

Reeza
Super User

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

jsphnwllms
Fluorite | Level 6

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
Tourmaline | Level 20

@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
Fluorite | Level 6

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 21384 views
  • 2 likes
  • 4 in conversation