DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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?


Accepted Solutions
Solution
‎09-03-2016 10:21 PM
Super User
Posts: 23,754

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

Posted in reply to kyleporter

Try adding DBMS=XLSX to the proc import statement. 

View solution in original post


All Replies
Solution
‎09-03-2016 10:21 PM
Super User
Posts: 23,754

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

Posted in reply to kyleporter

Try adding DBMS=XLSX to the proc import statement. 

New Contributor
Posts: 4

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

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.

Super User
Posts: 3,920

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

Posted in reply to kyleporter

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?

New Contributor
Posts: 4

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

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

Super User
Posts: 23,754

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

Posted in reply to kyleporter

Also, please include exact code and log. 

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

New Contributor
Posts: 4

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

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
Learner
Posts: 1

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

Posted in reply to kyleporter

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 5964 views
  • 1 like
  • 4 in conversation