topic Re: Importing xlsx files. in Microsoft Integration with SAS

Importing xlsx files.

sarahsasuser — Tue, 12 Mar 2013 21:29:50 GMT

I have SAS 9.3 installed, but 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;

Re: Importing xlsx files.

Reeza — Tue, 12 Mar 2013 21:47:20 GMT

Are you using SAS on Windows?

If so try importing via libname, then you can see the sheets and what SAS is seeing.

libname test excel "C;\user\test.xlsx";

Then check your libraries and see what the sheets look like. If you're after sheet4 for example:

data test;

set test.'Sheet4$'n;

run;

*Close the connection to the excel file;

libname test;

Re: Importing xlsx files.

Linlin — Tue, 12 Mar 2013 23:11:28 GMT

did the ; in FILE='C;\user\test.xlsx' give you problem?

Re: Importing xlsx files.

sarahsasuser — Wed, 13 Mar 2013 19:39:56 GMT

Hi Reeza,

I get the following error when I run the libname statement.

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

I googled it, and found that I need to download and install Microsoft's Data Connectivity Components, which I have done.

I'm running SAS 9.3 on a Windows 7 machine. That download should have fixed it, correct?

Re: Importing xlsx files.

SASKiwi — Thu, 14 Mar 2013 02:35:02 GMT

One possible reason for this error is the Microsoft components must match the "bitness" of SAS. If you are running 32-bit SAS you need the 32-bit components, if you are running 64-bit SAS you need the 64-bit components.

Re: Importing xlsx files.

sarahsasuser — Tue, 26 Mar 2013 21:43:11 GMT

Yes, it I have 32-bit office components, but 64-bit SAS. I think this is the issue.

Thanks.