Reading Excel file and determining data type

[anandbillava]

Is there any best way reading excel file without loosing data. Since sas will determine data type of each column with respect to first 8 rows of data u will end up having lot of missing values.

http://support.sas.com/kb/6/123.html

I tried all the tricks stated in the above link. But I could not get any solution.

[piggy]

try to use guessing rows=10000; to tell sas to read more rows to determine the data format, or save your excel file to csv file, then input.

[SASKiwi]

I am pretty sure the guessingrows option doesn't work with EXCEL imports, but the save to CSV is a good choice as guessingrows works with that.

Another way is to use a LIBNAME with the EXCEL option and then use DBSASTYPE to control the SAS column definitions.

A quick and dirty approach is to put a single quote into the first blank row of the columns in the spreadsheet you wish to be treated as character.

[deleted_user]

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.

[Reeza]

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.

[boschy]

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.

[SASPhile]

i used connect to excel in proc sql and it worked.

[Ksharp]

Hi.
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.


Ksharp

[Peter_C]

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.

If your data in excel do have an intended structure, then use that structure information in a data step (or sql) with the DBSASTYPE option. This tells excel how to deliver the data. When they don't conform, you will get missing values and perhaps "invalid data" messages.
The online doc for DBSASTYPE http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002261324.htm