topic proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit in SAS Programming

proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit

kyleporter — Sat, 03 Sep 2016 03:31:46 GMT

On a new install, PROC IMPORT and EXPORT are not working for xlsx files. Both SAS and MS Office are 64-bit. These PROCs work on two other computers I have. Running it on my new computer (details below) results in the following errors:

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

The procedure code is basic:

proc import datafile='C\Sample Data\xlsx data.xlsx' out=data1; run;

I recently installed SAS 9.4 on a Microsoft Surface Book running Windows 10. The installed modules/versions are Base SAS 9.4_M3, SAS_STAT 14.1, SAS_GRAPH 9.4_M3, High Performance Suite 2.2_M4, and SAS/ACCESS to PC Files 9.4_M3. Both SAS 9.4 and Microsoft Office are 64-bit installations.

The code above works on the other two computers on which I have run SAS 9.4. I haven't been able to determine what is different in the new setup that is causing the errors, unless it is simply Windows 10 (Surface book, doesn't work) versus Windows 7 (other 2 computers, works). But I haven't found any reported issues for importing xlsx files on Windows 10.

I currently have SAS 9.4 on a Dell PC running Windows 7 (separate employer, separate SAS license). Before I switched to the Surface Book I had an HP PC also running Windows 7. Both of these computers had 64-bit SAS and 64-bit office, and PROC IMPORT and PROC EXPORT both worked with xlsx files. I don't have the exact version details for these two computers right now (both are SAS 9.4), but I can get it if it will be helpful.

Any thoughts on what the problem might be?

Re: proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit

Reeza — Sat, 03 Sep 2016 04:09:34 GMT

Try adding DBMS=XLSX to the proc import statement.

Re: proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit

SASKiwi — Sat, 03 Sep 2016 06:00:26 GMT

Are there any differences in the version or setup of MS Office between the computers that work and those that don't? Could there be remnants of 32-bit Office interfering?

Re: proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit

Reeza — Sat, 03 Sep 2016 06:38:12 GMT

Also, please include exact code and log.

The code above has errors that may not be in your actual code or they may...

Re: proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit

kyleporter — Sun, 04 Sep 2016 02:21:04 GMT

That worked, thanks! In my other installs I didn't have to specify the DBMS; it was recognized by the file extension. But that is an easy solution to implement, so thanks.

Re: proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit

kyleporter — Sun, 04 Sep 2016 02:26:05 GMT

That is possible. Adding DBMS=xlsx worked so I'm good to go.

Re: proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit

kyleporter — Sun, 04 Sep 2016 02:30:18 GMT

Thanks for the solution. Just to be complete, I will include the full text and log below. All of the code was in the OP, but not all of the log.

proc import datafile='C\Sample Data\xlsx data.xlsx' out=data1; run;

3
4    proc import datafile='C:\Sample Data\xlsx data.xlsx' out=data1;
4  !                                                                 run;

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
Connection Failed.  See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.31 seconds
      cpu time            0.15 seconds

Re: proc import not working with xlsx files - new SAS 9.4 install - both SAS and office are 64-bit

sasuser1111 — Wed, 12 Apr 2017 21:31:22 GMT

I am having the same problem. I have sas9.4 t1m4. New install on windows 8.1. MS office 64 bit, sas 64 bit. I can't get it to work with proc import at all. I have tried writing the code and using the wizard - same errors as person who asked first with both. OR, it "runs" w/ DBMS=xlsx and doesn't like any options "ERROR: Import unsuccessful. See SAS Log for details"

PROC IMPORT DATAFILE= "path\file.xlsx"   OUT= work.newsasfile    DBMS=xlsx replace;
     RANGE="import_data$";
     GETNAMES=YES;
     MIXED=YES;
   SCANTEXT=YES;
RUN;

If I take out everything from range down, it runs but can't obviously pick the sheet I need.

The following code works, but I want to fix the above regardless. And for this code, I have to specify the range or it does not work...

libname inc xlsx "path\file.xlsx";
data inc_area; set inc."import_data$A1:U52"n; run;

any suggestions?

Thanks!