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!
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;
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.
Worked perfectly! I greatly appreciate your help!
@jsphnwllms please mark the appropriate solution as correct (not this response). Thanks!
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!
@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!
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.