topic Re: Import XLSX file in SAS Programming

Import XLSX file

hhchenfx — Tue, 19 Sep 2017 16:32:21 GMT

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,


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

Re: Import XLSX file

error_prone — Tue, 19 Sep 2017 16:59:02 GMT

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.

Re: Import XLSX file

ChrisBrooks — Tue, 19 Sep 2017 17:22:32 GMT

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

Re: Import XLSX file

hhchenfx — Tue, 19 Sep 2017 17:27:36 GMT

Thank you.

In my situation, XLSX is the only choice.

I am using SAS 9.3 64bit.

Re: Import XLSX file

Reeza — Tue, 19 Sep 2017 18:06:08 GMT

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.

Re: Import XLSX file

GraphGuy — Fri, 06 Oct 2017 12:29:49 GMT

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;