DATA Step, Macro, Functions and more

need to seperate full name to last and firstname using substring function

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

need to seperate full name to last and firstname using substring function

Need to seperate the comma delimited  full name to last name and first name. 

The word in front of the comma as the Last Name column and the word after the comma as First Name . I have tried with attached code and getting the errors like :-

NOTE: Invalid second argument to function SUBSTR at line 6059 column 12.

NAME=UNKNOWN LAST_NAME= FIRST_NAME=UNKNOWN _ERROR_=1 _N_=35

 

data namepart; 
set TEST; 
length LAST_NAME FIRST_NAME $45; 
LAST_NAME= substr(name,index(name,',') - 1); 
FIRST_NAME = trim(left(substr(name,index(name,',') + 1))); 
run;

 

 

 


Accepted Solutions
Solution
‎02-07-2017 11:50 AM
Trusted Advisor
Posts: 1,554

Re: need to seperate full name to last and firstname using substring function

[ Edited ]
Posted in reply to ambadi007

Check your code with light modification:

 

data namepart; 
   set TEST; 
         length LAST_NAME FIRST_NAME $45; 
pos = index(name, ',');
if pos > 0 then do; LAST_NAME= substr(name,1, pos - 1); FIRST_NAME = trim(left(substr(name,pos + 1)));
end; else put '>>> No comma in name, line=' _N_; run;

By the way, you can do:

strip(substr(name,index(name,',') + 1)))

instead using left(trim(...

 

You can also do:

last_name = scan(name,1 ',');
first_name = scan(name,2,',');

View solution in original post


All Replies
PROC Star
Posts: 7,468

Re: need to seperate full name to last and firstname using substring function

Posted in reply to ambadi007

Add a starting position to the line where you are trying to create lastname. i.e.,

 

LAST_NAME= substr(name,1,index(name,',') - 1); 

However, it sounds like you might have some names that are missing commas.

 

HTH,

Art, CEO, AnalystFinder.com

 

Super User
Posts: 5,497

Re: need to seperate full name to last and firstname using substring function

[ Edited ]
Posted in reply to ambadi007

In theory, this should be easy:

 

last_name = scan(name, 1, ',');

first_name = scan(name, 2, ',');

 

In practice, a little more care needs to be taken.  There may be no commas, for example.  (That may be why you ran into trouble with the INDEX function returning a zero.)  And if there is a blank after the comma, you may need to use:

 

first_name = left(scan(name, 2, ','));

 

And what should happen if there are actually two commas, such as a last name that contains ", Jr."

 

 

Solution
‎02-07-2017 11:50 AM
Trusted Advisor
Posts: 1,554

Re: need to seperate full name to last and firstname using substring function

[ Edited ]
Posted in reply to ambadi007

Check your code with light modification:

 

data namepart; 
   set TEST; 
         length LAST_NAME FIRST_NAME $45; 
pos = index(name, ',');
if pos > 0 then do; LAST_NAME= substr(name,1, pos - 1); FIRST_NAME = trim(left(substr(name,pos + 1)));
end; else put '>>> No comma in name, line=' _N_; run;

By the way, you can do:

strip(substr(name,index(name,',') + 1)))

instead using left(trim(...

 

You can also do:

last_name = scan(name,1 ',');
first_name = scan(name,2,',');
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1439 views
  • 0 likes
  • 4 in conversation