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
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.
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.
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.
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.
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.
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.
Ah, yes. There's an empty (hence numeric) variable called LASTNAME. I delete it and now it works. Thank you!
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.
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.