Error converting character to value to numeric

[joesmama]

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

[Kurt_Bremser]

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:

[ballardw]

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.

[PaigeMiller]

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.

[Tom · Accepted solution]

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.' 
;

[joesmama]

Thank you. Problem solved.