Importing a .xlsx file into SAS

[thummala]

Hi all,

May be this sounds like a silly question but I tried all the ways that I can and still get the error message.

I am trying to import a .xlsx file which has 10 sheets into SAS environment. I am using SAS 8 and  I am getting the following error.

proc import datafile = 'C:\Users\Rahul\Documents\SAS\SASexamples\Company_info.xlsx'

DBMS = xlsx OUT = company;

ERROR: DBMS type XLSX not valid for import.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used:

      real time           0.00 seconds

      cpu time            0.00 seconds

run;

My question is how can we import .xlsx file with multiple sheets into SAS environment.

Thank You.

[Jagadishkatam · Accepted solution]

Hi,

Your code is right, just a minor change is required. In DBMS please remove xlsx and put excel, like below

DBMS=excel

Thanks,

Jagadish

Editor's note: This 12-minute video steps you through the process of importing Excel data into SAS:

[damanaulakh88]

Hi,

Try this, this is working :-

PROC IMPORT OUT= WORK.EMPLOYEE

            DATAFILE= "C:\Users\Rahul\Documents\SAS\SASexamples\Company_info.xlsx"

            DBMS=EXCEL REPLACE ;

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

And if not then do one thing:-

Run this command:-

========

proc setinit;

run;

=========

And check if you have this in your log "---SAS/ACCESS Interface to PC Files". If this is not there you are not licensed to use SAS through this.

/Daman

[Cynthia_sas]

Hi,

Actually, I would recommend that you check the Tech Support area. I believe that XLSX files could NOT be imported until SAS 9.2, Phase 2 version. I think that even with SAS/ACCESS to PC Files, you could not import XLSX files from Office 2007/2010 until that release of SAS.

cynthia

[MichelleHomes]

Hi,

The error message you are getting is correct is you are using SAS Version 8 to try to import an .xlsx file.

SAS Access to PC File Formats (the underlying product you are using for PROC IMPORT) started supporting reading in .xlsx files (from Excel 2007 onwards) from SAS 9.2. If you save your .xslx file to an .xls file it may work (can't remember whether the syntax is the same for SAS Version 😎 or alternatively upgrade your SAS version to the latest release SAS 9.3.

SAS 9.1.3 documentation showing that Excel 2007 files are not supported - http://support.sas.com/onlinedoc/913/docMainpage.jsp?_topic=acpcref.hlp/a000611256.htm

SAS 9.2  documentation showing that Excel 2007 files are supported - http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003103761.htm

Kind Regards,

Michelle

[thummala]

Thank you all for your suggestions.

I did saved the file in .xls and tried the following, it worked (I just did tried a single sheet).

PROC IMPORT OUT= WORK.company DATAFILE= "C:\Users\Rahul\Documents\SAS\SASexamples\Company_info.xls"

            DBMS=EXCEL REPLACE;

     GETNAMES=YES;

RUN;

Again thank you all for your input.

[sarahsasuser]

Hi All,

I'm having similar problem. I have SAS 9.3 installed. SAS seems to ignore the sheet= statement because the resulting dataset 'test' is made up of data from the first sheet in my excel file. Any idea how to fix this?

PROC IMPORT OUT=work.test

        FILE='C;\user\test.xlsx'

        DBMS=EXCEL REPLACE;

    sheet='sheet4';

  getnames=yes;

RUN;

[sarahmerza]

Same problem! Using SAS 9.3 too

the error message :

ERROR: DBMS type EXCEL not valid for import

How to solve this? I even changed XLSX to XLS.

Nothing works.

[MichelleHomes]

Hi sarahmerza,

The error message you are getting may be due to your environment not having the SAS/Access to PC File Formats licensed as described in this usage note, http://support.sas.com/kb/6/976.html

Kind Regards,

Michelle

[Ron_MacroMaven]

I recommend saving data as *.csv: Comma Separated Values, which is a text file.

then you do not have to worry about

* version

* bitness 64bit SAS cannot read MS Excel *.xls? nor Access *.mdb

* Access to PC files

Ron Fehd  formerly SAS Tech Support @MyCompany

[sarahmerza]

Thanks Michelle for the info. Now I know why I can't import Excel files.

Thanks Ron Fehd for the suggestion. I converted my data into csv file but I encountered another problem.

Error Message : NOTE: Invalid data for Answer67 in line 53 144-144

RULE:	----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---
53	52,1,1,1,1,3,3,3,3,3,2,3,3,3,3,3,3,2,3,2,3,2,2,2,2,3,2,3,3,3,2,2,3,2,3,3,3,3,3,3,2,3,3,2
	89  ,2,3,3,3,3,2,2,3,3,2,3,2,3,3,2,3,3,3,3,3,3,3,2,3,3,3,3,*,3,3,2,2,3,3,2,2,3,2,3,2,3,2,3,3
	177  ,3,2,3,3,3,3,3,3,3,2,2,2,3,2,3,2,2,3,3,3,3,3,3,2,3,3,3,3,3,3,3,2,3,3,3,2,3,3,2,3,3,2,2,3
	265  ,3,3,3,3,2,2,2,3,3,3,3,3,2,3,3,3,2,2,3,3,3,3,2,2,3,2,2,3,3,3,3,3,3,2,3,3,3,3,3,3,3,3,3,3
	353  ,3,2,3,3,3 362

Got the same message for quite a few number of variables. I'm not sure what is wrong.

I've check the original data but I don't think I find any * in it.

[sarahmerza]

my mistake! :smileylaugh: I solved the problem!

[Praveen_kumar]

IN 9.2 or 9.3.. we can import a XLS or XLSX using SAS ODBC Concept.

[safework]

How do you import Excel worksheet .XLSX in to SAS 9.3 using a Macro? I have used import Macro with Infile. Does it work with datafile? Or can I use Infile statement to import .XLSX fie.

[snoopy369]

You are asking things that don't fully make sense in context.  No, you cannot use infile to read in an xlsx file, at least not without some extraordinary work.  What do you mean by 'use a macro'?  If you need to import a single .xlsx file, read the OP and the accepted answer.  If you need to import many xlsx files, then I suggest a new question with the details of how they are named and such.