EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer

SAS Error: Class not registered

Question: When we try to read excel file into SAS, or load ACCESS databases into SAS, either by libname or import procedure, we easily find some errors in the log:

Error: Connect: Class not registered
Error: Error in the Libname statement
ERROR: DBMS type EXCEL (ACCESS) not valid for import.
Error: Procedure ***** not Found.

Answer: First let's check how many ways to read excel files into SAS:
1):    PROC IMPORT DATAFILE="directory\filename.xlsx"
        OUT=dataout DBMS=excel2010 REPLACE; RUN;

2)    libname lib1 oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0;
        Data Source='directory\filename.xlsx';
        Extended Properties=Excel 12.0";

        data test;     set lib1.'tabname$'n; run; /*don't forget dollar $ & 'n' */

3)         LIBNAME myexcel "directory\filename.xlsx";

Similarly there are two ways to load data tables from ACCESS database:
1)   libname datalib "directory\databasename.mdb";

2)   proc import out=dataout datatable="sheet's name" dbms=access replace;
      database="directory\databasename.mdb"; run;

You can try both ways to load the excel or ACCESS database, Sometimes, it might be due to some small mis-spelling typos.

Second, as mentioned in previously tutorial, there is special licence to read excel files or SPSS files directly, the licence is: SAS/ACCESS to PC files, you can run the code: proc setinit; run; to see if you have that licence on your SAS or not. For example, if you run Proc IML ...., then the error showed up: Procudure IML not found; then this is because you don't have SAS/IML module purchased.

As you may find there are several ways online to deal with this error, for example, check and change the Microsoft Access Connectivity Engine (ACE) registry keys.

Personally, I don't recommend this for people without too much knowledge about registry, changing the registry can easily mess up your computer or server, even the professionals has to be very careful when doing the changes. Also most of time it's not due to this, most of time you actually had the Microsoft Access Connectivity Engine (ACE) registry keys. You can check whether you had the ACE registry key by the following steps, again, very carefully:

1) Select Start --> Run, type regedit in the Open box to display the Registry.
2) In the registry tree, select HKEY_LOCAL_MACHINE --> Software --> Microsoft --> Office --> 12.0 --> Access Connectivity Engine ;

Some other way to treat the problem might be related with installing SAS PC Files Server by using pcfiles in libname statement, this is not recommended, since you may have to re-install/repair your SAS.

Finally let's go the easiest solution: as you may know, SAS 9.1.3 is a 32-bit release, SAS 9.2 and 9.3 are 64-bit release. The above errors usually happen on 9.2 or 9.3 ("There was no problem when we run this on 9.1 previously! :) :)").

The trouble-maker might just be your Microsoft office. By default, when you install your office 2003 or 2007, it's automatically 32-bit. To check whether you have 32-bit or 64-bit Excel on Office 2010:

1.Start an Office product, such as Excel. In Excel, click the File tab and then click Help in the list on the left.
2.The version of Excel is shown under the About Microsoft Excel on the right.
3. There you will see the version of office and whether its 32 bit or 64 bit.

If you found out it's 32-bit on your excel, no wonder you had the above errors in the log. Simply ask the technician to install 64-bit Microsoft office, No worries, they are the same cost!

Speaking of formating excel via SAS DDE, you may have to change your code(Office14) for Microsoft Office 2010:

      options noxwait noxsync;
      x '"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"';

In case if you have more questions, you can submit your questions via Contact us Here, We'll try to answer your questions ASAP.

SAS Programmer's BookShelf

Related links:

Continue to next: How to read large csv file: Number of names found is less than...   SAS tutorial home
Back to: How to generate dummy variables automatically?   Statistics tutorial home