Using libname statement in 64 bit SAS to interact with 32 Excel

Question

I have 64 bit SAS 9.4 installed and 32 bit Office 2007. When I had 32 bit SAS 9.3 installed, I was able to use a libname statement where the Excel file was the library and the "filename" that follows after the period was in fact a worksheet or named range in the Excel file. For example, you start with a libname statement

libname exceltst path="C:\Users\username\Documents\test.xlsx";

And, then later you can reference a named range in this with exceltst.stuff, assume "stuff" is a named range.

So, is this still possible? I am guessing it has to do with 64 bit sas and 32 bit excel but I am wondering if there is some variation that still works. I am able to use a proc export to export to the excel file and it works just fine but I have tried 20 variations on the libname and can't get it to work. The error I get is:

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

Answer 1

With SAS 9.4 or later 9.3 updates, you have another option: dbms=XLSX or dbms=XLS, both of which work regardless of bitness of office (As they're not using the JET engine or ACE engine). Still requre ACCESS to PC FILES, but they should work in mixed bitness systems.

Answer 2

If you can install SAS PC Files Server, you can still do this with the PCFILES destination. Installing the PC Files Server software is out of the scope of an answer here, but google about for instructions, they are readily available. You can install the PC Files Server on your desktop, or on a central server if that's more convenient for your workplace.

Once you have it installed, you just access it by:

libname mylib pcfiles path="blah\blah\blah.xlsx";

Just like you did before, but with a different dbms type.

Answer 3

The problem is you need the 64 bit Excel libraries for 64 bit SAS to interact with.

Chris Hemedinger posted a few work arounds to this issue as few years ago. http://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/

If you have ACCESS/ODBC, another option is to create an ODBC connection to the Excel spreadsheet.