Libname doesn't work in 9.4

[michelconn]

I am having an issue with SAS 9.4. The code below does not work in 9.4 but does work in 9.3 (I have both versions installed).

libname kdot 
"\\mriglobal\kcshares\Projects\110933\KDOT Crash Data for Freeways in Johnson and Wyandotte Counties\KDOT Crash Data as Received from KDOT.xlsx"
getnames=yes mixed=yes;

If I had to guess it was because I was missing an update for 9.4 but was wondering if someone could help figure out why the code does not work in 9.4. 

The error message I get is:

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

[Tom]

Most likely your SAS 9.4 is 64 bit and your SAS 9.3 and MicroSoft Office are 32 bit.

Probably the easist thing to do is to use the new XLSX engine instead of the EXCEL engine. Otherwise you will need to get SAS PC Files Server running and figure out how to use that.

libname kdot XLSX "myfile.xlsx" ;

[art297]

I agree with @Tom but, if you use the xlsx engine, be sure to remove the "getnames=yes mixed=yes" statements as they're not supported in the xlsx engine.

Art, CEO, AnalystFinder.com

[michelconn]

You are correct 9.3 and excel and 32 bit and 9.4 is 64 bit. I tried the code you mentiond and got the following error:

ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.

I should have mentioned I tried changing the engine before and got this error.

I did remove the getnames and mixed statements.

[art297]

Do you license SAS Access for PC Files and, if so, is it installed?

Art, CEO, AnalystFinder.com

[michelconn]

Yes I have SAS Access and as far as I can tell it is installed on the machine. Atleast in the SAS 9.4 install folder there is an Access folder. Plus I don't have any issues with proc import only the libname statement. 

[art297]

You can run proc import using the dbms=xlsx engine?

Art, CEO, AnalystFinder.com

[michelconn]

Yes, proc import using dbms=xlsx works. 

I was using the libname statement because I find it is easier to deal with documents that have a lot of sheets. 

[art297]

Which maintenance version are you using? The xlsx engine wasn't added until SAS9.4, Maintenance 2

Art, CEO, AnalystFinder.com

[Tom]

Are your sheetnames valid membernames? You could try setting the VALIDMEMNAME option to EXTEND.

[michelconn]

Yes the sheets are valid names, I got the code to work in SAS 9.3. I tried the VALIDVARNAME=ANY option in 9.4

[ballardw]

The VALIDVARNAME setting would not effet table names based on sheet names.

If you make a very simple work book with maybe one cell populated (A1 with simple column name and A2 with a value like 5) and the default Sheet1 name can you read that?

[michelconn]

Thanks for the suggestions, unfortunately it did not work. I feel like at this point something was installed incorrectly or maybe there is an issue with having two version of SAS on my computer.

[Tom]

You should open a support ticket with SAS.  They can look into the details of your version of SAS and your XLSX and help you see if the issue is your installation or a bug in SAS. http://support.sas.com 

[michelconn]

I'll give that a shot thanks.