SCAN and SUBSTR character-to-numeric error

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

SCAN and SUBSTR character-to-numeric error

[ Edited ]

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)Smiley SadColumn).
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


Accepted Solutions
Solution
‎06-15-2016 04:40 PM
Super User
Posts: 5,352

Re: SCAN and SUBSTR character-to-numeric error

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


All Replies
Super Contributor
Posts: 414

Re: SCAN and SUBSTR character-to-numeric error

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.

Occasional Contributor
Posts: 5

Re: SCAN and SUBSTR character-to-numeric error

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. 

Super Contributor
Posts: 414

Re: SCAN and SUBSTR character-to-numeric error

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.

Occasional Contributor
Posts: 5

Re: SCAN and SUBSTR character-to-numeric error

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. 

 

Solution
‎06-15-2016 04:40 PM
Super User
Posts: 5,352

Re: SCAN and SUBSTR character-to-numeric error

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.

Occasional Contributor
Posts: 5

Re: SCAN and SUBSTR character-to-numeric error

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

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 406 views
  • 4 likes
  • 3 in conversation