I have tried changing the Guessingrows option in the Registry Editor but it doesn't seem to work with Excel files.
I really wish they would provide a simpler work around for this since the source date I am using is sort of funky when saved as csv.
If your file is CSV read the file using Proc Import. Then copy the code from the log and modify it to read correctly by changing the formats required. You can use ALT+mouse to select the data without the row numbers.
I've found that the only failsafe way to read Excel data values that might vary is to write specific DATA step logic.
For example, any columns in Excel (CSV) that might be a problem are read in as $ character variables. I then check the values as per the intended data type and use INPUT () only when the value has been validated.
If you're importing Excel XLS spreadsheet files, that is tricky. But if saving the XLS as CSV first is an option, try that.
I think it does work. and I also test it.
The problem occurred is beacuse of a variable has both numeric and character value,
and SAS only allow one of both type.
So the way to solve it is to use ' mixed=yes' statement in 'proc import'.
Or change it into csv and use proper informat to input.
you have to decide
data or code
If you have invalid data, how do you want to handle it?
There is no fail-safe for all the possible problems that can happen, to deliver data you can call clean and sufficient.
So decide the number of errors to tolerate before rejecting the input and requiring better quality.