DATA Step, Macro, Functions and more

Import XLSX file

Accepted Solution Solved
Reply
Super Contributor
Posts: 506
Accepted Solution

Import XLSX file

Hi Everyone,

Importing xlsx file into SAS is very complicate.

I want import my file (attached). The whole sheet1 will be imported as text.

I run the below code in my PC and it works fine.

Then I run it in my office PC and error show up. This is the error I saw few times and lookup solution but no help.

I copy the ERROR notice and the log for "proc setinit;run;" and I have ---SAS/ACCESS Interface to PC Files

Please anyone can help me with that.

Thank you so much,

HC


PROC IMPORT OUT=WORK.sample
		DATAFILE="C:\Users\a_sample.xlsx"
		DBMS=EXCEL REPLACE ;
	RANGE="Sheet1$";
	MIXED=YES;
	Getnames=no;
run;

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.05 seconds
cpu time 0.01 seconds

 

---Base SAS Software
14NOV2017
---SAS/STAT
14NOV2017
---SAS/GRAPH
14NOV2017
---SAS/ETS
14NOV2017
---SAS/FSP
14NOV2017
---SAS/OR
14NOV2017
---SAS/AF
14NOV2017
---SAS/IML
14NOV2017
---SAS/QC
14NOV2017
---SAS/SHARE
14NOV2017
---SAS/LAB
14NOV2017
---SAS/ASSIST
14NOV2017
---SAS/CONNECT
14NOV2017
---SAS/INSIGHT
14NOV2017
---SAS/EIS
14NOV2017
---SAS/GIS
14NOV2017
---SAS/SHARE*NET
14NOV2017
---SAS Enterprise Miner
14NOV2017
---MDDB Server common products
14NOV2017
---SAS Integration Technologies
14NOV2017
---SAS/Secure Windows
14NOV2017
---SAS Text Miner
14NOV2017
---SAS/Genetics
14NOV2017
---SAS Enterprise Guide
14NOV2017
---SAS Bridge for ESRI
14NOV2017
---OR OPT
14NOV2017
---OR PRS
14NOV2017
---OR IVS
14NOV2017
---OR LSO
14NOV2017
---SAS/ACCESS Interface to DB2
14NOV2017
---SAS/ACCESS Interface to Oracle
14NOV2017
---SAS/ACCESS Interface to Sybase
14NOV2017
---SAS/ACCESS Interface to PC Files
14NOV2017
---SAS/ACCESS Interface to ODBC
14NOV2017
---SAS/ACCESS Interface to OLE DB
14NOV2017
---SAS/ACCESS Interface to Teradata
14NOV2017
---SAS/ACCESS Interface to MySQL
14NOV2017
---SAS Enterprise Miner for Desktop
14NOV2017
---SAS/IML Studio
14NOV2017
---SAS Workspace Server for Local Access
14NOV2017
---SAS/ACCESS Interface to Netezza
14NOV2017
---SAS/ACCESS Interface to Aster nCluster
14NOV2017
---SAS/ACCESS Interface to Greenplum
14NOV2017
---SAS/ACCESS Interface to Sybase IQ
14NOV2017
---DataFlux Trans DB Driver
14NOV2017
---SAS Framework Data Server
14NOV2017
---Reserved for Dataflux
14NOV2017
---SAS Add-in for Microsoft Excel
14NOV2017

 

 

 


Accepted Solutions
Solution
‎10-06-2017 06:10 PM
SAS Employee
Posts: 1,056

Re: Import XLSX file

One general suggestion ... rather than trying to import the whole Excel sheet (which includes a lot of title stuff, etc), I recommend to always specify just the range of cells with the data. And then once you're doing that, I also suggest using getnames-yes. The spreadsheet will generally import much more cleanly & usable, when you specify the range. For example:

 

PROC IMPORT OUT=WORK.sample
DATAFILE="U:\a_sample.xlsx"
DBMS=EXCEL REPLACE ;
RANGE="Sheet1$A11:Q18";
MIXED=YES;
Getnames=yes;
run;

 

In this specific case, if using dbms=EXCEL isn't working on one particular computer, I would recommend bringing up the spreadsheet in Excel, and then re-saving it in the older "Excel 97-2003" format (the extension will then be .xls rather than .xlsx). And then you can try using SAS' dbms=XLS, which I believe uses code which SAS developers wrote to import the spreadsheet (whereas I believe dbms=EXCEL uses the Microsoft Jet engine ... or something like that).

 

PROC IMPORT OUT=WORK.sample
DATAFILE="U:\a_sample.xls"
DBMS=XLS REPLACE ;
RANGE="Sheet1$A11:Q18";
MIXED=YES;
Getnames=yes;
run;

 

import.png

View solution in original post


All Replies
Regular Contributor
Posts: 226

Re: Import XLSX file

First problem: Excel is not suitable for storing data, because the file- structure is not compatible to normal data storage. Importing an Excel file almost always forces post-processing steps in order to use the dataset afterwards.

Using SAS 9.4 try libname xlsx or dbms=xlsx with proc import. Setting guessingrows option in proc import to higher value can he help.

Valued Guide
Posts: 596

Re: Import XLSX file

Posted in reply to error_prone

It could be related to this problem http://support.sas.com/kb/43/933.html

Super Contributor
Posts: 506

Re: Import XLSX file

Posted in reply to error_prone

Thank you.

 

In my situation, XLSX is the only choice.

I am using SAS 9.3 64bit.

 

HC

Super User
Posts: 24,010

Re: Import XLSX file

You likely don't have the same versions (Excel or SAS) on the machines - ie 32 vs 64 bit somewhere. 

 

Try using DBMS=XLSX as suggested earlier.

 

You may need the PC FILE SERVER if you're having issues related to different bits, ie SAS is 64 bit but Excel is 32 bit.

Solution
‎10-06-2017 06:10 PM
SAS Employee
Posts: 1,056

Re: Import XLSX file

One general suggestion ... rather than trying to import the whole Excel sheet (which includes a lot of title stuff, etc), I recommend to always specify just the range of cells with the data. And then once you're doing that, I also suggest using getnames-yes. The spreadsheet will generally import much more cleanly & usable, when you specify the range. For example:

 

PROC IMPORT OUT=WORK.sample
DATAFILE="U:\a_sample.xlsx"
DBMS=EXCEL REPLACE ;
RANGE="Sheet1$A11:Q18";
MIXED=YES;
Getnames=yes;
run;

 

In this specific case, if using dbms=EXCEL isn't working on one particular computer, I would recommend bringing up the spreadsheet in Excel, and then re-saving it in the older "Excel 97-2003" format (the extension will then be .xls rather than .xlsx). And then you can try using SAS' dbms=XLS, which I believe uses code which SAS developers wrote to import the spreadsheet (whereas I believe dbms=EXCEL uses the Microsoft Jet engine ... or something like that).

 

PROC IMPORT OUT=WORK.sample
DATAFILE="U:\a_sample.xls"
DBMS=XLS REPLACE ;
RANGE="Sheet1$A11:Q18";
MIXED=YES;
Getnames=yes;
run;

 

import.png

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 1043 views
  • 0 likes
  • 5 in conversation