topic Re: Reading Excel file and determining data type in SAS Programming

Reading Excel file and determining data type

anandbillava — Wed, 10 Nov 2010 22:18:52 GMT

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.

Re: Reading Excel file and determining data type

piggy — Thu, 11 Nov 2010 00:16:11 GMT

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.

Re: Reading Excel file and determining data type

SASKiwi — Thu, 11 Nov 2010 01:17:49 GMT

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.

Re: Reading Excel file and determining data type

deleted_user — Wed, 02 Feb 2011 23:49:31 GMT

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.

Re: Reading Excel file and determining data type

Reeza — Wed, 02 Feb 2011 23:59:16 GMT

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.

Re: Reading Excel file and determining data type

boschy — Tue, 08 Feb 2011 23:35:04 GMT

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.

Re: Reading Excel file and determining data type

SASPhile — Wed, 09 Feb 2011 16:42:26 GMT

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

Re: Reading Excel file and determining data type

Peter_C — Thu, 10 Feb 2011 11:29:52 GMT

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

Re: Reading Excel file and determining data type

Ksharp — Wed, 23 Feb 2011 07:36:03 GMT

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