SAS 9.4 Error in SAS import..connection failed & error in libname statement

Reply
New Contributor
Posts: 4

SAS 9.4 Error in SAS import..connection failed & error in libname statement

I recently purchased a new computer which lead to a new install for SAS and Microsoft Office.  When trying to use the import wizard, I get the following error: 

 

SAS/STAT 14.1
SAS/ETS 14.1
SAS/OR 14.1
SAS/IML 14.1
SAS/QC 14.1

NOTE: Additional host information:

X64_8PRO WIN 6.2.9200 Workstation

NOTE: SAS initialization used:
real time 1.59 seconds
cpu time 1.14 seconds

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

 

I googled the error and found multiple articles saying that this was due to a problem with Office being 32-bit and SAS and Windows being 64-bit.  I uninstalled Office and reinstalled a 64-bit version of Office.  Unfortunately this didn't fix the error.  I thought that maybe it had to do with the fact that the excel files I was trying to upload were originally saved using a 32-bit office package.  As such, I created new excel files but it still doesn't work.  I also trying saving the files as a regular xls file instead od xlsx but this also failed.  Please help!

 

Super User
Posts: 3,238

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

[ Edited ]

Do you have the SAS product SAS/ACCESS for PC Files installed and licensed?

 

What bitness is SAS - 32 or 64 bit? The bitness of Windows doesn't matter.

 

Also please post your LIBNAME statement.

New Contributor
Posts: 4

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

Hello,

 

I am running 64-bit SAS.  

 

I am not sure if I have SAS/ACCESS for PC Files installed and licensed.  Can you tell me how to check this?

 

I do not have a libname statement since I am just using the Import Wizard.

 

Super User
Posts: 3,238

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

To verify SAS/ACCESS Interface to PC Files is installed: proc product_status; run;

New Contributor
Posts: 4

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

Thank you.  After running this, I can confirm that SAS/ACCESS to PC files is installed.

Super User
Posts: 3,238

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

You should be able to try @Tom's suggestions then.

Super User
Super User
Posts: 6,850

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

If you are using that recent a version of SAS then just use the XLSX (or XLS) engine instead of the Excel engine.

proc import dbms=xlsx .....

You can even use a libref instead of PROC IMPORT.

libname in xlsx 'myfile.xlsx';
proc copy inlib=in outlib=work; run;
New Contributor
Posts: 4

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

Do you know of a way to set dbms=xlsx when using the import navigation window as opposed to actually typing out the code to run proc import?  With this error, I select my file, hit next and it just closes out the window before allowing me to get any further.

Super User
Posts: 3,238

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

I suggest you try the coded version first as a test. If that works OK then the problem must lie with the Import Wizard. Are you using EG's Import Wizard or something else?

Super User
Posts: 7,465

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement


E_Page wrote:

Do you know of a way to set dbms=xlsx when using the import navigation window as opposed to actually typing out the code to run proc import?  With this error, I select my file, hit next and it just closes out the window before allowing me to get any further.


If you are using EG, it sets its dbms= type according to the file type. It also first uses the MS-supplied module to convert the Excel file to text and runs a data step to read from that, AFAIK. Since EG can work with remote servers, it does not use proc import at all (the server only gets the data through the IOM connection as a flat file in the Workspace).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,850

Re: SAS 9.4 Error in SAS import..connection failed & error in libname statement

[ Edited ]

I just tried this with EG 6.1 and the import tool gave me a couple of ways to work around Excel engine not working.

 

1) In the import step it gave me the option to embed the data into the program.  This basically made a data step with in-line data from the sheet in the Excel file that I picked.

 

2) I then also tried telling it to use SAS/Access and this failed just as yours did with CLASS error.  But I was able to click on the program code it generated and try to edit it. EG said that I couldn't edit the code but that it could make a copy. So I did that.  Then I was able to change the PROC IMPORT code from using DBMS=Excel to instead use DBMS=XLSX and the code ran fine.  

 

I like this method because it also let me edit the PROC DATASETS step that it generated and use it to REMOVE the crazy FORMATS and INFORMATS that the PROC IMPORT step had added.  There is no need to attach formats or informats to character variables or most numeric variables.  You really only need FORMATS for things like dates and times.

 

/* --------------------------------------------------------------------
   Code generated by a SAS task
   
   Generated on Wednesday, February 22, 2017 at 1:08:53 PM
   By task:     Import Data Wizard
   
   Source file: C:\Users\XXX.xlsx
   Server:      Local File System
   
   Output data: WORK.patients_subset1
   Server:      Local
   -------------------------------------------------------------------- */

/* --------------------------------------------------------------------
   PROC IMPORT reads the data directly from the Excel source file.
   -------------------------------------------------------------------- */

PROC IMPORT
        DATAFILE="C:\Users\XXX.xlsx"
        OUT=WORK.patients_subset1
        REPLACE
        DBMS=XLSX;
    RANGE="DEMOGRAPHICS$A1:H125";
    GETNAMES=YES;
RUN;

/* --------------------------------------------------------------------
   PROC DATASETS modifies the attributes of the columns within the
   output data set.
   -------------------------------------------------------------------- */

PROC DATASETS LIBRARY=WORK NOLIST;
    MODIFY patients_subset1;
        FORMAT _all_ ;
        INFORMAT _all_ ;
    RUN;
QUIT;

 

Ask a Question
Discussion stats
  • 10 replies
  • 1275 views
  • 1 like
  • 4 in conversation