topic Excel Import to SAS 9.4 in SAS Data Management

Excel Import to SAS 9.4

liju — Thu, 17 Dec 2015 02:05:56 GMT

I am trying to import an excel data with a variable contains both numeric and alpha numeric values. Unfortunately SAS determinies the variable type by looking at first observations and the variable got converted to Numeric type. Hence the alpha numeric values are not appearing in the imported data in SAS. Is there any way to import the data in specific format that we define.

Background: I am trying to automated a reporting process where there are several excel files with multiple sheets. Idea is to use only program. My Excel is 32 bit and SAS is 64 bit. Hence using DBMS=EXCELCS in proc import statement.

Please help.

Re: Excel Import to SAS 9.4

SASKiwi — Thu, 17 Dec 2015 02:21:03 GMT

One workaround is to insert a dummy first row of data with character values in it where you are getting the wrong type, then in SAS delete the first row after reading it in.

You could also try changing the column type in Excel from the default General to Text.

A third option is to do a "text to columns" translation on the affected columns using Excel's Data tab.

Re: Excel Import to SAS 9.4

liju — Thu, 17 Dec 2015 02:27:14 GMT

Hi , Thank you for a quick reply on this. Yes this solution will work for sure. But since I am trying to automate the process i didnt want the user to open every excel file and manually change the type. It is not actually practical as there several of such excel files. Any thoughts ?

Thanks a lot for helping.

Regards,

Liju.

Re: Excel Import to SAS 9.4

SASKiwi — Thu, 17 Dec 2015 03:46:47 GMT

OK, if this applies to multiple spreadsheets then you could read them via a LIBNAME with the EXCEL engine.

Then you can use the DBSASTYPE option to enforce the data type that SAS reads the columns in as. This assumes that the column names you get are always consistent though. There are some useful examples in this paper:

http://support.sas.com/resources/papers/proceedings11/076-2011.pdf

Re: Excel Import to SAS 9.4

RW9 — Thu, 17 Dec 2015 09:43:45 GMT

Well, your problem is that your are using Excel as a data transfer file format, which it is not. You will encounter all kinds of issues and problems by doing it this way. Ideally you would want to, in conjunction with the data vendor, setup a data transfer agreement which details file structure and uses a proper data transfer medium, such as CSV or XML. This is the most robust safe method for both parties as the agreement defines the files. Whilst you can have an agreement with Excel files, there can still be a fair few problems, such as this where Excel strusture is not database like - i.e. each cell can be its own format. Simply put, fixing the source of these problems, i.e. not using Excel, would be the optimal approach.

If you still insist on using Excel, there is a registry hack, also there is the mixed=yes options:

https://communities.sas.com/t5/SAS-Procedures/GUESSINGROWS-EXCEL-to-SAS/td-p/18505

However, what happens if the data changes, i.e. its all numeric, then there are characters, your code would need to change, what happens if there are special characters, hidden information, cells move about etc. Far too many problems using Excel.

Re: Excel Import to SAS 9.4

ballardw — Thu, 17 Dec 2015 19:09:00 GMT

@liju wrote:

I am trying to import an excel data with a variable contains both numeric and alpha numeric values. Unfortunately SAS determinies the variable type by looking at first observations and the variable got converted to Numeric type. Hence the alpha numeric values are not appearing in the imported data in SAS. Is there any way to import the data in specific format that we define.

Minor correction in understanding of what is going on: Excel tells SAS what the variables are and uses a very small number of rows to determine whether to consider character.

This type of behavior is one reason why Excel is generally a poor medium for interchanging data.