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: 17,829

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

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: 17,829

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

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,106

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

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: 17,829

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

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

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

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
  • 1304 views
  • 1 like
  • 4 in conversation