SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Excel Import to SAS 9.4

Reply
New Contributor
Posts: 4

Excel Import to SAS 9.4

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.

 

Super User
Posts: 3,261

Re: Excel Import to SAS 9.4

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.   

New Contributor
Posts: 4

Re: Excel Import to SAS 9.4

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.

 

 

Super User
Posts: 3,261

Re: Excel Import to SAS 9.4

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

 

Super User
Super User
Posts: 7,997

Re: Excel Import to SAS 9.4

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.

Super User
Posts: 11,343

Re: Excel Import to SAS 9.4


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.

Ask a Question
Discussion stats
  • 5 replies
  • 507 views
  • 1 like
  • 4 in conversation