BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jazzman
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
Reeza
Super User

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

 

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

Jazzman
Obsidian | Level 7

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.

ballardw
Super User

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 .

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

Jazzman
Obsidian | Level 7

Thanks a lot, it worked!

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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