BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ambadi007
Quartz | Level 8

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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

3 REPLIES 3
art297
Opal | Level 21

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

 

Astounding
PROC Star

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."

 

 

Shmuel
Garnet | Level 18

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,',');

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 15265 views
  • 0 likes
  • 4 in conversation