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

Hi all,

I've got an inconsistent list of names that I need to separate into last name and first name. I've used SCAN and SUBSTR before but I'm having trouble this time. Here's an example of the data I have in a variable called NAME:

 

AMY

CHLOE, GREG

LING, DANIEL

RAZICK, OMAR

VELJANI, JOVI (JOHN)

ALFASA

 

So obviously they aren't entirely consistent. I am just trying to pull out the first word because I need to match up some pre-post assessments that the same people took and I can do that roughly using last name. In the cases where the person only put their first name they put it that way both times, so I will have to assume that AMY pre is the same as AMY post. 

 

My code, trying both SUBSTR and SCAN, where a comma and a space are the possible delimiters: 

 

data pre;

set pre;

    lastname=substr(name,1);

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

run;

 

both SUBSTR and SCAN functions in this form give me the same error: 

"NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
144:14 
NOTE: Invalid numeric data, 'AMY' , at line 144 column 14.
RESPONDENTID=123456789

...  

NAME=AMY

...

"

 

Why is it trying to convert character to numeric then tell me the name isn't numeric?  Is it because my first observation contains a single word rather than two words? In that case I would expect it to behave exactly like Example 1 here: http://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p0jshdjy2z9...   Here there is an entry of a single word ("Leonardo") and it comes out of the SCAN function as the first and the last word, where in my situation it won't do anything. 

 

SAS 9.4 on Windows server

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Check the PROC CONTENTS results to see if your incoming data set already contains LASTNAME or LASTNAME2.  In particular, check to see if one of those variables is already defined as numeric.

View solution in original post

6 REPLIES 6
jklaverstijn
Rhodochrosite | Level 12

Do a PROC CONTENTS on dataset pre. It will tell you what type name is. If it is numeric a previous step where it was created introduces the unexpected behaviour. Please share with us full code and sample data to examine. Now we only have fragments that do not tell the whole story.

 

Regards,

- Jan.

mega
Fluorite | Level 6

I made a working dataset, renamed the variable of interest from that messy name to NAME, made the name data upper case, and sorted the data by name. 


data pre;

   set data.pre_18mar2016 (rename=(NAME__LAST__FIRST____OPEN_ENDED_=NAME));
name=upcase (name);
run;

 

proc sort data=pre;
   by name;
run;


proc contents data=pre; run;

 

According to PROC CONTENTS, NAME as well as NAME__LAST__FIRST___OPEN_ENDED_ are character with lengths of 26. 

jklaverstijn
Rhodochrosite | Level 12

Then will all respect the error doesn't make sense. Can you provide the entire log? The message tells it happens at line 144 so there must be more.

 

- Jan.

mega
Fluorite | Level 6

I agree it doesn't make sense!

 

The line numbers are just from running it several times trying different things within the same SAS session. The code begins with me defining my libname and turning ODS graphics off, stuff that I have at the beginning of every program. I work for a hospital so I can't copy my entire log since it contains confidential data. I guess I'll contact SAS. 

 

Astounding
PROC Star

Check the PROC CONTENTS results to see if your incoming data set already contains LASTNAME or LASTNAME2.  In particular, check to see if one of those variables is already defined as numeric.

mega
Fluorite | Level 6

Ah, yes. There's an empty (hence numeric) variable called LASTNAME. I delete it and now it works. Thank you!

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