BookmarkSubscribeRSS Feed
liju
Calcite | Level 5

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.

 

5 REPLIES 5
SASKiwi
PROC Star

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.   

liju
Calcite | Level 5

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.

 

 

SASKiwi
PROC Star

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1546 views
  • 1 like
  • 4 in conversation