topic Re: Using proc import after upgrading to Windows 10 in SAS Data Management

Using proc import after upgrading to Windows 10

n6 — Wed, 16 Oct 2019 16:38:00 GMT

I upgraded to Windows 10 (not because I wanted to but because the place where I work made everybody do it). There is Excel data I've brought in many time using the macro (at bottom). But now the macro doesn't work. Here is the error I'm getting:

Connect: Class not registered
Error in the LIBNAME statement.

I tried a few variations, including doing "Import Data" from the File menu to let SAS wrote new code itself and then use that, but it doesn't work. In looking if someone has come across this problem before on here, I get the impression that the only way to deal with this is to reinstall SAS. Is that really the case? A hate to sound like a whiner, but stuff like this is a mojo killer. It's like planning a long and detailed trip around the world and then when it comes time to go, you can't get out of your house because your can't figure out how to work the doorknob. It's demoralizing. I've spent two hours on this and have gotten nowhere. Any help is greatly appreciated. Here's the marco code. I'm pretty sure that the fact that it's a macro instead of regular code isn't the issue. Before Upgrading to Windows 10 I used this code hundreds of times without any problem.

%MACRO FROM_EXCEL (folder, excel_file, name);
PROC IMPORT OUT= WORK.&NAME

DATAFILE = "C:\Path\&EXCEL_FILE..xlsx"

DBMS=XLSX REPLACE; RANGE = "&NAME$";

GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES;
RUN;
%MEND FROM_EXCEL;

Re: Using proc import after upgrading to Windows 10

JosvanderVelden — Wed, 16 Oct 2019 17:05:42 GMT

I assume your operating system is 64bit and ms office 32bit. Have a look at this note: http://support.sas.com/kb/63/750.html

Re: Using proc import after upgrading to Windows 10

n6 — Wed, 16 Oct 2019 17:18:53 GMT

What if I have 64 bit of both, which I do?

Re: Using proc import after upgrading to Windows 10

SASKiwi — Wed, 16 Oct 2019 19:33:58 GMT

This is more an issue between the bitness of SAS and Excel. Is your SAS 32 or 64 bit?

Re: Using proc import after upgrading to Windows 10

n6 — Wed, 16 Oct 2019 19:48:00 GMT

I am running SAS Version 9.4 TS Level 1M1.

It says "X64_8PRO," which I'm guessing means 64 bit.

It also says

Operating System Information

Windows Version 6.2.9200

Does that help?

Re: Using proc import after upgrading to Windows 10

Tom — Wed, 16 Oct 2019 19:51:16 GMT

Not WINDOWS bitness. Microsoft OFFICE bitness (AKA, Word, Excel, etc.)

Re: Using proc import after upgrading to Windows 10

n6 — Wed, 16 Oct 2019 19:56:12 GMT

My operating system is 64 bit and also my Microsoft Office 64.

Re: Using proc import after upgrading to Windows 10

JosvanderVelden — Thu, 17 Oct 2019 16:12:56 GMT

Can you run a test with the following code:

PROC EXPORT DATA=sashelp.class 
     OUTFILE='c:\demo.xlsx' 
     DBMS=XLSX REPLACE;
     SHEET='Class';
RUN;

PROC IMPORT DATAFILE="c:\demo.xlsx"
     DBMS=XLSX
     OUT=WORK.CLASS REPLACE;
     SHEET='Class'; 
RUN;
 
PROC PRINT DATA=WORK.CLASS;
RUN;

NOTE: In most cases MS Office installations are still 32bit. To check: https://support.jetglobal.com/hc/en-us/articles/219403267-How-to-determine-whether-you-have-32-bit-or-64-bit-Excel If you Office in 32bit and windows 64bit you need PC Files Server https://support.sas.com/downloads/package.htm?pid=1285

Re: Using proc import after upgrading to Windows 10

n6 — Thu, 17 Oct 2019 16:26:16 GMT

I just checked an my MS Office is indeed 64-bit.

I ran the things you listed and it worked. It output the little SAS dataset to an Excel sheet and then it brought it back in and it all worked well, including the proc print. Should I try it with my data? I notice that in my code (the code that worked until they put Windows 10 on my machine), I have Range = "&NAME$"; to indicate which sheet to get from the Excel file but that the code I just used from you uses Sheet = "Class";

Re: Using proc import after upgrading to Windows 10

JosvanderVelden — Thu, 17 Oct 2019 17:09:53 GMT

I believe the dollar-sign is no needed longer needed. Not sure when that changed.

A couple of questions:

Do your worksheets have spaces in the names?
Can names of worksheets or ranges be longer than 32 characters?

Best regards, Jos

Re: Using proc import after upgrading to Windows 10

Tom — Thu, 17 Oct 2019 17:08:50 GMT

You might need to setup SAS PC FILES SERVER as a buffer between your SAS code and the Microsoft code. But that is normally only needed to overcome the mixed 32/64 bit environments. If you are having trouble with SAS accessing the Microsoft tools you might also have trouble with the PC FILES server accessing the Microsoft tools.

You might want to open a ticket with SAS support to see if they can help you debug.

The advantage of the using the XLSX engine instead is that it does not depend on any Microsoft code running on the SAS server. You can even use that when running SAS on Unix.

The disadvantage is that it does not have all of the features of the EXCEL engine.

Re: Using proc import after upgrading to Windows 10

n6 — Thu, 17 Oct 2019 17:12:05 GMT

In the past I've had issues with embedded blanks in Excel sheet names and thus by now I've made it my habit to never have blanks and to fill them in with _. And I don't make them longer than 32 because I've had issues with that in the past too.

Re: Using proc import after upgrading to Windows 10

n6 — Thu, 17 Oct 2019 17:13:59 GMT

I already put PC Files Server on, way back when, so presumably them updating my computer to Window 10 didn't change that.

I'm going to try to use the test code the other person posted and hopefully it'll work. Yes, if we go long enough I'll have to go directly to SAS support.