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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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:

 

Thanks,
Jag

View solution in original post

16 REPLIES 16
Jagadishkatam
Amethyst | Level 16

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:

 

Thanks,
Jag
damanaulakh88
Obsidian | Level 7

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
SAS Super FREQ

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
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
thummala
Obsidian | Level 7

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
Quartz | Level 8

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
Calcite | Level 5

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
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Ron_MacroMaven
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Calcite | Level 5

my mistake! :smileylaugh: I solved the problem!

Praveen_kumar
Calcite | Level 5

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

safework
Calcite | Level 5

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
Barite | Level 11

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.

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!

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
  • 16 replies
  • 385322 views
  • 15 likes
  • 12 in conversation