LIBNAME error on access database import

[lady8506]

I am trying to import a MSAccess database and it's not working. I am running Windows 7 64 bit. My MS Office software is 2010. My SAS is 9.4 for 64 bit. Therefore, every piece of documentation I have found which talks about version incompatibility and offers a solution is NOT WORKING. 

Here is what I've done:

1    PROC IMPORT TABLE = "Trends" DBMS = ACCESS OUT = WORK.SASTrends REPLACE;
2    DATABASE = "C:\Users\bennetde\Documents\Trends in Early Onset study\Trends in Early Onset
2  ! (clean).accdb";
3    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.73 seconds
      cpu time            0.07 seconds

And: 

4    LIBNAME libref ACCESS PATH = "C:\Users\bennetde\Documents\Trends in Early Onset
4  ! study\Trends in Early Onset (clean).accdb";
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement

Please help me solve this problem so I can import my MSAccess database. Thank you.

[Kurt_Bremser]

You max have a bitness problem (Office 32bit, SAS 64)

Or Access to PC Files is not licensed or not properly installed.

[SASKiwi]

I suspect your MS Office is 32-bit and because your SAS is 64-bit you are getting the classic "Class not registered" error. To bridge the bitness difference you can either use the SAS PC File Server which is a download from SAS or you could download the 64-bit ACE engine from Microsoft. We use the latter and it works well. 

[lady8506]

It does say MS Office is 32 bit. Thanks! I will try to fix this and then see what happens.

[rogerjdeangelis]

Importing a 64bit or 32bit  MS Access table into SAS using R

I hesitate to answer because MS has so many different structures for
MS access databases. Really beed a sample from your database.

If you have IML/R you can create a SAS dataset directly without
going through a STATA dataset, as I have done below.



HAVE a SAS supplied mdb database with table customers
=====================================================

  C:\Progra~1\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb d:\mdb\demo.mdb

WANT  SAS dataset work.customers
===

       CUSTOMER_
Obs        ID       STATE    ZIP_CODE    COUNTRY

  1     14324742     CA        95123     USA
  2     14569877     NC        27514     USA
  3     14898029     MD        20850     USA
  4     15432147     MI        49001     USA
  5     18543489     TX        78701     USA
  6     19783482     VA        22090     USA
  7     19876078     CA        93274     USA
  8     26422096     NA        75014     France
  9     26984578     NA         5110     Austria
 10     27654351     NA         5010     Belgium
 11     28710427     HV         3607     Netherlands
 12     29834248     NA            Z     Britain
 13     31548901     BC            Z     Canada
 14     38763919     NA         1405     Argentina
 15     39045213     SP         1051     Brazil
 16     43290587     NA            Z     Japan
 17     43459747     NA         3181     Australia
 18     46543295     NA            Z     Japan
 19     46783280     NA         2374     Singapore
 20     48345514     NA            Z     United Arab Emirates


WORKING CODE
============

    customers = sqlQuery(myDB, paste('select * from customers'));

FULL SOLUTION

* SAS comes with sample access databses so lets copy one for demo purposes.

x "copy C:\Progra~1\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb d:\mdb\demo.mdb";
demo.mdb has a customer table

Here I am using 64bit windows, 64 bit SAS, 64 bit office in the first program
and 32 bit R in the second. R installs bothe 32bit and 64bit under the same root.


* this creates a SAS dataset much like IML/R
%utl_submit_wps64('
options set=R_HOME "C:/Program Files/R/R-3.3.1";
libname wrk "%sysfunc(pathname(work))";
proc r;
submit;
library(RODBC);
myDB<-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=d:/mdb/demo.mdb");
mtcarsmdb<-sqlQuery(myDB, paste("select * from customers"));
mtcarsmdb;
close(myDB);
endsubmit;
import r=mtcarsmdb data=wrk.mtcarsmdb;
run;quit;
');

mtcarsmdb is a SAS dataset in the parent work directory

* Using 32 bit SAS

%utl_submit_r32("
library(RODBC);
library(foreign);
myDB <- odbcConnectAccess('d:/mdb/demo.mdb',uid='admin',pwd='');
sqlTables(myDB);
customers<- sqlQuery(myDB, paste('select * from customers'));
write.dta(customers,convert.factors='string',version = 10L, 'd:/dta/customers.dta')
");

* you do not need this with IML/R;
filename imp 'd:/dta/customers.dta' lrecl=32756;
proc import
   out=work.customers
   file=imp
   dbms=DTA replace;
run;

proc print data=customers;
var customer_id--country;
run;quit;

32 Bit R log LOG
library(RODBC);
library(foreign);
myDB <- odbcConnectAccess('d:/mdb/demo.mdb',uid='admin',pwd='');
sqlTables(myDB);
customers<- sqlQuery(myDB, paste('select * from customers'));
write.dta(customers,convert.factors='string',version = 10L, 'd:/dta/customers.dta')

       TABLE_CAT  TABLE_NAME   TABLE_TYPE

10 d:\\mdb\\demo   Customers        TABLE
11 d:\\mdb\\demo   Employees        TABLE
12 d:\\mdb\\demo     Invoice        TABLE
13 d:\\mdb\\demo      Orders        TABLE

SAS

807   filename imp 'd:/dta/customers.dta' lrecl=32756;
808   proc import
809      out=work.customers
810      file=imp
811      dbms=DTA replace;
812   run;

NOTE:    Variable Name Change.  Customer ID -> Customer_ID
NOTE:    Variable Name Change.  Zip Code -> Zip_Code
NOTE:    Variable Name Change.  First Ordered Date -> First_Ordered_Date
NOTE: The import data set has 20 observations and 10 variables.
NOTE: WORK.CUSTOMERS data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              345.03k
      OS Memory           13800.00k
      Timestamp           03/01/2017 12:22:32 PM
      Step Count                        106  Switch Count  0


813   proc print data=customers;
814   var customer_id--country;
815   run;

NOTE: There were 20 observations read from the data set WORK.CUSTOMERS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.05 seconds
      user cpu time       0.00 seconds
      system cpu time     0.06 seconds
      memory              326.71k
      OS Memory           13800.00k
      Timestamp           03/01/2017 12:22:32 PM
      Step Count                        107  Switch Count  0

815 !     quit;


FYI IML/R and WPS/R can create SAS datasets from all these input data types.

utl_submit_wps64('
options set=R_HOME "C:/Program Files/R/R-3.3.1";
libname wrk "%sysfunc(pathname(work))";
library(foreign);
proc r;
submit;
library(rio);
library(foreign);
library(haven);
mtcarscsv  <-import("d:/rio/mtcars.csv");
mtcarstsv  <-import("d:/rio/mtcars.tsv");
mtcarsxml  <-import("d:/rio/mtcars.xml");
mtcarshtml  <-import("d:/rio/mtcars.html");
mtcarsjson  <-import("d:/rio/mtcars.json");
mtcarsxlsx  <-import("d:/rio/mtcars.xlsx");
mtcarsdta   <-read.dta("d:/rio/mtcars.dta");
mtcarssav   <-read_sav("d:/rio/mtcars.sav");
mtcarsdbf   <-import("d:/rio/mtcars.dbf");
mtcarsods   <-import("d:/rio/mtcars.ods");
mtcarsdif   <-read.DIF("d:/rio/mtcars.dif",header=TRUE,transpose=TRUE);
endsubmit;
import r=mtcarscsv data=wrk.mtcarscsv;
import r=mtcarstsv data=wrk.mtcarstsv;
import r=mtcarsxml data=wrk.mtcarsxml;
import r=mtcarsjson data=wrk.mtcarsjson;
import r=mtcarsdta data=wrk.mtcarsdta;
import r=mtcarsxlsx data=wrk.mtcarsxlsx;
import r=mtcarshtml data=wrk.mtcarshtml;
import r=mtcarssav data=wrk.mtcarsav;
import r=mtcarsdbf data=wrk.mtcarsdbf;
import r=mtcarsdif data=wrk.mtcarsdif;
run;quit;
');

%utl_submit_wps64('
options set=R_HOME "C:/Program Files/R/R-3.3.1";
libname wrk "%sysfunc(pathname(work))";
proc r;
submit;
library(RODBC);
myDB<-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=d:/mdb/HelpfulContacts.mdb");
mtcarsmdb<-sqlQuery(myDB, paste("select * from mtcars"));
mtcarsmdb;
close(myDB);
endsubmit;
import r=mtcarsmdb data=wrk.mtcarsmdb;
run;quit;
');

[Kurt_Bremser]

Or you could go the totally easy way and export your data from Access to a textual format (csv), and use a simple data step to read that.

No licenses or modules needed, and no bitness problems. And will work 20 years from now, while you may never know what idiocies MS does with their format next, and through what hoops other software has to jump to deal with it.

TEXTUALITY RULES!