BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michelconn
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
michelconn
Quartz | Level 8

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. 

View solution in original post

15 REPLIES 15
Tom
Super User Tom
Super User

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
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

michelconn
Quartz | Level 8

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
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

michelconn
Quartz | Level 8

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
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

Tom
Super User Tom
Super User

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

michelconn
Quartz | Level 8

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
Super User

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
Quartz | Level 8
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
Super User Tom
Super User

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
Quartz | Level 8
I'll give that a shot thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 8875 views
  • 4 likes
  • 4 in conversation