Hi.
I am reading in an Excel spreadsheet and need to convert a character variable to a numeric one. I'm trying to create a new variable using the INPUT command
Days_to_Event=input(Days_to_Event_A, 8..);
but I keep getting this error:
NOTE: Invalid argument to function INPUT at line 480 column 20
Most of the converted data is missing. What am I missing here? Is it possible that for the variable in question in the Excel spreadsheet that some of the values are character and some numeric? I need help moving forward.
All help is appreciated.
Thank you
Why are you telling INPUT to only use the first 8 bytes of the character variable? Remember the INPUT function does not care if the WIDTH used on the informat is larger than the LENGTH of the string you gave it to read. So use the maximum width the informat allows. Which is 32.
If the column in your EXCEL file has a mix of numeric and character cells then SAS will make the variable CHARACTER.
If SAS is not already printing the values that did not convert then you can add your own code to test and display them.
Days_to_Event=input(Days_to_Event_A, ??32.);
if missing(Days_to_Event) and not missing(Days_to_Event_A) then
put 'NOTE: ' _n_= Days_to_Event_A= :$quote. 'could not be converted to a number.'
;
Maxim 3: Know Your Data.
What strings are contained in your variable? Do they constitute valid (for the informat used) numbers?
Which means they contain only digits, maybe a decimal dot, and a leading sign.
If in doubt, post examples here into a window opened with this button:
Run Proc Contents on your data set and share the results.
Also, it is best practice to copy from the log the entire data step or procedure along with all the note, messages, warnings or errors. Then on the forum open a text box using the </> icon above the message window and paste all the copied text. The text box is important because the forum software will reformat text pasted into the main message window which reduces the usefulness of diagnostic characters SAS often supplies. Also the entire data step or procedure is important because many issues can relate to problems caused on previous lines such as missing semicolons, mismatched quotes or parentheses. And when SAS says "column 20" we can tell which is column 20.
And the complete log likely shows enough information, such as the extremely likely not valid to create a number using the 8. informat in the log as well. Example:
153 data junk; 154 dta='not a number'; 155 days=input(dta,8.); 156 run; NOTE: Invalid argument to function INPUT at line 155 column 8. dta=not a number days=. _ERROR_=1 _N_=1 NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values. Each place is given by: (Number of times) at (Line):(Column). 1 at 155:8 NOTE: The data set USER.JUNK has 1 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds
I strongly suspect that some values of Days_to_Event_A either have text like "missing" "NA" or the number with something else like "7 days". None of those can be read with a W. informat and would require example values and possibly expected results.
Days_to_Event=input(Days_to_Event_A, 8..);
There are two dots after the number 8, that is an error. But the error message you are getting about invalid input means your variable Days_to_Event_A is the wrong type, for INPUT to work it must be character (and obviously it is not character).
And yes, next time show us the log for a DATA step or PROC, showing us the code that appears in the log plus all warnings, errors and notes for the PROC or DATA step with the error, pasted in to the text box that appears when you click on the </> icon, as described by the others.
Why are you telling INPUT to only use the first 8 bytes of the character variable? Remember the INPUT function does not care if the WIDTH used on the informat is larger than the LENGTH of the string you gave it to read. So use the maximum width the informat allows. Which is 32.
If the column in your EXCEL file has a mix of numeric and character cells then SAS will make the variable CHARACTER.
If SAS is not already printing the values that did not convert then you can add your own code to test and display them.
Days_to_Event=input(Days_to_Event_A, ??32.);
if missing(Days_to_Event) and not missing(Days_to_Event_A) then
put 'NOTE: ' _n_= Days_to_Event_A= :$quote. 'could not be converted to a number.'
;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: