Help using Base SAS procedures

Import numeric data from xls

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Import numeric data from xls

[ Edited ]

Hello,

 

I'm trying to import a number of Excel files to SAS using the IMPORT procedure. My data have an ID string in the first column and numeric data (only numbers plus a few periods for missing data) in the other columns.

 

Now my problem is that SAS imports some of the numeric variables as numeric and some as characters. There doesn't seem to be any pattern. I even changed the Excel cell formats to numbers to be sure everything is alright. I'm working with a lot of different tables so it's not feasible to correct the data types manually.

 

What am I doing wrong? What exactly is the criterion for SAS to treat variables as characters instead of numbers?


Accepted Solutions
Solution
‎10-27-2016 04:32 AM
Super User
Super User
Posts: 7,970

Re: Import numeric data from xls

The problem is with Excel.  Excel is unstrcutured/uncontrolled.  In each cell you could put anything you like, including pictures and control objects etc.  Most sensible data processing environments fix the structure of the dataset (be it a database or SAS or anything else) so that programming can be done on the data efficiently.  When SAS reads in data from Excel, it looks through a number of observations and guesses what the type should be - this is the guessingrows registry parameter.  

Now if your importing data, it is better to get the data in a proper machine readbale format (with an assciated validated and repeatable procedure).  Say you save the Excel file as CSV, you can then read in the file using datastep infile, and at that point you would fix informats, formats, lengths etc. and so have full control over the data that is read in.

This will help with CSV import.  

View solution in original post


All Replies
Super User
Posts: 19,822

Re: Import numeric data from xls

Do you have missing values/blanks in your excel files? 

 

Look into GUESSINROWS option but it's a system registry change. 

Occasional Contributor
Posts: 12

Re: Import numeric data from xls

Hi Reeza,

 

I have periods as missing values in the data but for some reason SAS imports a couple of empty rows after the last observation and those are of course blanks.

Super User
Posts: 11,343

Re: Import numeric data from xls

In a foreign data source such as Excel a period is a character. If the import procedure runs into characters within the rows it examines, I believe 20 for Excel, then the column is treated as character.

 

You may be better off by  deleting the periods in the Excel file and reimporting where you use a search and replace for contents of cell are .

Solution
‎10-27-2016 04:32 AM
Super User
Super User
Posts: 7,970

Re: Import numeric data from xls

The problem is with Excel.  Excel is unstrcutured/uncontrolled.  In each cell you could put anything you like, including pictures and control objects etc.  Most sensible data processing environments fix the structure of the dataset (be it a database or SAS or anything else) so that programming can be done on the data efficiently.  When SAS reads in data from Excel, it looks through a number of observations and guesses what the type should be - this is the guessingrows registry parameter.  

Now if your importing data, it is better to get the data in a proper machine readbale format (with an assciated validated and repeatable procedure).  Say you save the Excel file as CSV, you can then read in the file using datastep infile, and at that point you would fix informats, formats, lengths etc. and so have full control over the data that is read in.

This will help with CSV import.  

Occasional Contributor
Posts: 12

Re: Import numeric data from xls

Thanks a lot, it worked!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 337 views
  • 0 likes
  • 4 in conversation