topic Re: Libname doesn't work in 9.4 in SAS Programming

Libname doesn't work in 9.4

michelconn — Mon, 17 Apr 2017 13:42:38 GMT

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.

Re: Libname doesn't work in 9.4

Tom — Mon, 17 Apr 2017 13:56:50 GMT

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" ;

Re: Libname doesn't work in 9.4

art297 — Mon, 17 Apr 2017 14:07:47 GMT

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

Re: Libname doesn't work in 9.4

michelconn — Mon, 17 Apr 2017 14:12:43 GMT

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.

Re: Libname doesn't work in 9.4

art297 — Mon, 17 Apr 2017 14:18:31 GMT

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

Art, CEO, AnalystFinder.com

Re: Libname doesn't work in 9.4

michelconn — Mon, 17 Apr 2017 20:49:56 GMT

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.

Re: Libname doesn't work in 9.4

art297 — Mon, 17 Apr 2017 21:11:43 GMT

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

Art, CEO, AnalystFinder.com

Re: Libname doesn't work in 9.4

michelconn — Mon, 17 Apr 2017 21:15:34 GMT

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.

Re: Libname doesn't work in 9.4

art297 — Mon, 17 Apr 2017 21:22:56 GMT

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

Art, CEO, AnalystFinder.com

Re: Libname doesn't work in 9.4

Tom — Mon, 17 Apr 2017 21:42:42 GMT

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

Re: Libname doesn't work in 9.4

michelconn — Tue, 18 Apr 2017 15:02:20 GMT

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

Re: Libname doesn't work in 9.4

ballardw — Tue, 18 Apr 2017 20:17:38 GMT

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?

Re: Libname doesn't work in 9.4

michelconn — Wed, 19 Apr 2017 18:33:52 GMT

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.

Re: Libname doesn't work in 9.4

Tom — Wed, 19 Apr 2017 19:55:16 GMT

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

Re: Libname doesn't work in 9.4

michelconn — Wed, 19 Apr 2017 20:34:04 GMT

I'll give that a shot thanks.

Re: Libname doesn't work in 9.4

michelconn — Mon, 24 Apr 2017 15:45:16 GMT

SAS support helped me find a solution. I had to use the code below for my 64 bit version of SAS to communicate with the 32 bit version of excel.

libname dot PCFILES PATH="\\global\shares\Projects\110933\data.xlsx";

I also had to uninstall PC file serve since I had both a 32 bit and 64 bit version installed. I reinstalled 32 bit PC file server and the code above worked.