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

Importing xlsx files.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
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
Respected Advisor
Posts: 3,069

Re: Importing xlsx files.

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
Grand Advisor
Posts: 17,464

Re: Importing xlsx files.

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: 104

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.

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

Solution
‎03-13-2013 10:35 PM
Respected Advisor
Posts: 3,069

Re: Importing xlsx files.

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: 104

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.

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

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