Help using Base SAS procedures

Importing a .xlsx file into SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Importing a .xlsx file into SAS

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.


Accepted Solutions
Solution
‎02-18-2013 10:54 PM
Trusted Advisor
Posts: 1,137

Re: Importing a .xlsx file into SAS

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

Thanks,
Jag

View solution in original post


All Replies
Solution
‎02-18-2013 10:54 PM
Trusted Advisor
Posts: 1,137

Re: Importing a .xlsx file into SAS

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

Thanks,
Jag
Frequent Contributor
Posts: 81

Re: Importing a .xlsx file into SAS

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

SAS Super FREQ
Posts: 8,865

Re: Importing a .xlsx file into SAS

Posted in reply to damanaulakh88

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

Trusted Advisor
Posts: 1,321

Re: Importing a .xlsx file into SAS

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 8) 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

Occasional Contributor
Posts: 16

Re: Importing a .xlsx file into SAS

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.

Frequent Contributor
Posts: 106

Re: Importing a .xlsx file into SAS

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;

New Contributor
Posts: 3

Re: Importing a .xlsx file into SAS

Posted in reply to sarahsasuser

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.

Trusted Advisor
Posts: 1,321

Re: Importing a .xlsx file into SAS

Posted in reply to sarahmerza

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

Regular Contributor
Posts: 227

Re: Importing a .xlsx file into SAS

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

New Contributor
Posts: 3

Re: Importing a .xlsx file into SAS

Posted in reply to Ron_Fehd_macro_maven

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.

New Contributor
Posts: 3

Re: Importing a .xlsx file into SAS

Posted in reply to sarahmerza

my mistake! :smileylaugh: I solved the problem!

N/A
Posts: 1

Re: Importing a .xlsx file into SAS

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

New Contributor
Posts: 3

Re: Importing a .xlsx file into SAS

Posted in reply to Praveen_kumar

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.

Super Contributor
Posts: 253

Re: Importing a .xlsx file into SAS

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 16 replies
  • 222021 views
  • 9 likes
  • 12 in conversation