SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Importing xlsx files.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 106
Accepted Solution

Importing xlsx files.

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;


Accepted Solutions
Solution
‎03-13-2013 10:35 PM
Super User
Posts: 3,474

Re: Importing xlsx files.

Posted in reply to sarahsasuser

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.

View solution in original post


All Replies
Super User
Posts: 21,494

Re: Importing xlsx files.

Posted in reply to sarahsasuser

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;

Frequent Contributor
Posts: 106

Re: Importing xlsx files.

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?

Super Contributor
Posts: 1,636

Re: Importing xlsx files.

Posted in reply to sarahsasuser

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

Solution
‎03-13-2013 10:35 PM
Super User
Posts: 3,474

Re: Importing xlsx files.

Posted in reply to sarahsasuser

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.

Frequent Contributor
Posts: 106

Re: Importing xlsx files.

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

Thanks.

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 1032 views
  • 1 like
  • 4 in conversation