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";
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
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.
Thank you Tom!
* 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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: