DATA Step, Macro, Functions and more

Reading Excel file and determining data type

Reply
Contributor
Posts: 72

Reading Excel file and determining data type

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.
Occasional Contributor
Posts: 14

Re: Reading Excel file and determining data type

Posted in reply to anandbillava
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.
Super User
Posts: 3,250

Re: Reading Excel file and determining data type

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.
N/A
Posts: 0

Re: Reading Excel file and determining data type

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.
Super User
Posts: 19,770

Re: Reading Excel file and determining data type

Posted in reply to deleted_user
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.
Contributor
Posts: 71

Re: Reading Excel file and determining data type

Posted in reply to anandbillava
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.
Super Contributor
Posts: 673

Re: Reading Excel file and determining data type

Posted in reply to anandbillava
i used connect to excel in proc sql and it worked.
Super User
Posts: 10,020

Re: Reading Excel file and determining data type

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
Valued Guide
Posts: 2,177

Re: Reading Excel file and determining data type

Posted in reply to anandbillava
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
Ask a Question
Discussion stats
  • 8 replies
  • 959 views
  • 0 likes
  • 9 in conversation