BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JohnSAScom
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
JohnSAScom
Quartz | Level 8

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

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

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.

 

JohnSAScom
Quartz | Level 8

Thank you Tom!

rogerjdeangelis
Barite | Level 11


* 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
JohnSAScom
Quartz | Level 8

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

 

 

 

JohnSAScom
Quartz | Level 8

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 6609 views
  • 0 likes
  • 3 in conversation