02-18-2013 10:22 PM
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
My question is how can we import .xlsx file with multiple sheets into SAS environment.
02-18-2013 11:06 PM
Try this, this is working :-
PROC IMPORT OUT= WORK.EMPLOYEE
DBMS=EXCEL REPLACE ;
And if not then do one thing:-
Run this command:-
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.
02-19-2013 12:30 AM
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.
02-19-2013 12:38 AM
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
02-19-2013 01:13 AM
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"
Again thank you all for your input.
03-08-2013 12:05 PM
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
04-24-2013 05:06 AM
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
04-24-2013 09:22 AM
I recommend saving data as *.csv: Comma Separated Values, which is a text file.
then you do not have to worry about
* bitness 64bit SAS cannot read MS Excel *.xls? nor Access *.mdb
* Access to PC files
Ron Fehd formerly SAS Tech Support @MyCompany
04-25-2013 02:51 AM
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
|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.
09-19-2013 01:07 PM
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.
09-19-2013 01:11 PM
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.
Need further help from the community? Please ask a new question.