topic Re: LIBNAME error in SAS Programming

LIBNAME error

JohnSAScom — Wed, 06 Jul 2016 12:20:32 GMT

Hi,

I am trying to export a SAS dataset to MS Access using the Libname statement. When I code this:

libname DatIn ACCESS PATH="C:\Temp\ExpAcc.accdb";

I get this error:

22 libname DatIn ACCESS PATH="C:\Temp\ExpAcc.accdb";

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement

According to page “The LIBNAME Statement for Access and Excel on Microsoft Windows” in the SAS/ACCESS help, this is one of two options for assigning a libref to an MS Access file. The other option is

libname DatIn "C:\Temp\ExpAcc.accdb";

both of which produce the same error.

Thank you,

John

Re: LIBNAME error

Tom — Wed, 06 Jul 2016 12:32:55 GMT

Your SAS installation and you Microsoft Office installation are using different number of bits (32 vs 64). Check with your system admins to see if you can change one or the other.

Re: LIBNAME error

JohnSAScom — Wed, 06 Jul 2016 12:50:32 GMT

Thank you Tom!

Re: LIBNAME error

rogerjdeangelis — Wed, 06 Jul 2016 15:04:40 GMT



* Make a copy of the mdb just in case;
* I prefer the older "full" version of access because access meta data is available;
* you can always move the data to the newer version;

x "copy d:\mdb\db.mdb d:\mdb\want.mdb";

* prep SAS dataset for R;
data "d:/sd1/class.sas7bdat";
  set sashelp.class;
run;quit;

%utl_submit_r32(
library(RODBC);
library(haven);
class<-read_sas('d:/sd1/class.sas7bdat');
myDB <- odbcConnectAccess(""d:/mdb/want.mdb"",uid=""admin"",pwd="""");
sqlSave(myDB,class,rownames=FALSE);
);

libname mdb "d:\mdb\want.mdb";
proc contents data=mdb._all_;
;run;quit;
libname mdb clear;

he CONTENTS Procedure

         Directory

ibref         MDB
ngine         ACCESS
hysical Name  d:\mdb\want.mdb
ser           Admin


                 DBMS
         Member  Member
  Name   Type    Type

  class  DATA    TABLE


Data Set Name        MDB.class    Observations          .
Member Type          DATA         Variables             5
Engine               ACCESS       Indexes               0
Created              .            Observation Length    0
Last Modified        .            Deleted Observations  0
Protection                        Compressed            NO
Data Set Type                     Sorted                NO
Label
Data Representation  Default
Encoding             Default


         Alphabetic List of Variables and Attributes

#    Variable    Type    Len    Format    Informat    Label

3    AGE         Num       8                          AGE
4    HEIGHT      Num       8                          HEIGHT
1    NAME        Char    255    $255.     $255.       NAME
2    SEX         Char    255    $255.     $255.       SEX
5    WEIGHT      Num       8                          WEIGHT

Re: LIBNAME error

JohnSAScom — Thu, 07 Jul 2016 12:35:24 GMT

Hi Tom,

In my case, changing either installation is not an option. As it turns out, I should not have been attempting to use the LIBNAME statement at all. The context is that I was trying to export a SAS dataset to MS Access, and I thought I could use the LIBNAME to do so, however, in my case, there is a good probability that the MS Access database I'm trying to create already exists, so the LIBNAME statement would not work. I needed to use PROC EXPORT so that I could specify the REPLACE option.

My solution is like this:

proc export

data = SAS_DS

dbms = accesscs

outtable = EXPACCESS

replace;

database = "c:\dir\NewDB.accdb";

run;

For me, the key was the value of the DBMS= option. When I read the SAS/ACCESS help page entitled

File Format-Specific Reference for the IMPORT and EXPORT Procedures, it said

"For an existing .mdb you can specify DBMS=ACCESSCS when using the client/server model and SAS identifies the version of Access for you."

I mistakenly interpreted this to imply that I could not use it with .accdb files, but you can, as explained in the SAS/ACCESS help page entitled "PROC EXPORT".

Thank you again for your help!

John

Re: LIBNAME error

JohnSAScom — Thu, 07 Jul 2016 12:44:52 GMT

Hi Roger,

Please see my reply to Tom. In my case, the MS Access database I'm trying to create probably already exists, so I would not be able to use the LIBNAME statement. Your solution looks interesting, but, in my case, if I wanted to connect to an existing MS Access database using the LIBNAME statement, I think an easier solution would be to use the SAS PC Files Server in the libname:

libname mylibref pcfiles path="C:\dir\MyAccessDB.accdb";

Thank you,

John