Hi All,
I am trying to connect SAS to Access DB (.mdb) l, located in Network drive,
Tried libname:
libname myLib Access "I:\Database_p.mdb";
and got the following error,
ERROR: The ACCESS engine cannot be found.
ERROR: Error in the LIBNAME statement.
Tried proc sql odbc:
libname myacc odbc required=
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};
DBQ=I:\Database_p.mdb";
and got the following error,
ERROR: The ODBC engine cannot be found.
ERROR: Error in the LIBNAME statement.
Please suggest
Run: proc setinit; run; to confirm what products you have licenced. You should be looking for SAS/ACCESS to PC Files or SAS/ACCESS to ODBC. If these products are not in your SAS licence and not installed this could explain the errors you are seeing.
Importing a table from a MS Access database into SAS without Access to PC-Files
You may be able to use the IML interface to R and even create a SAS dataset.
I had to use 32bit R because I could not get 64bit SAS to work,
even though my SAS and MS Access is 64bit.
There are other packages but I am more familiar with RODBC.
HAVE MDB that shipped with SAS
===============================
* copy the mdb that comes with SAS, I added the class dataset;
x "copy C:\Progra~1\sashome\SASFoundation\9.4\access\sasmisc\demo.mdb d:\mdb\demo.mdb";
10 d:\\mdb\\demo class TABLE
11 d:\\mdb\\demo Customers TABLE
12 d:\\mdb\\demo Employees TABLE
13 d:\\mdb\\demo Invoice TABLE
14 d:\\mdb\\demo Orders TABLE
CLASS
Up to 40 obs from sashelp.class total obs=19
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69 112.5
2 Alice F 13 56.5 84
3 Barbara F 13 65.3 98
4 Carol F 14 62.8 102.5
5 Henry M 14 63.5 102.5
6 James M 12 57.3 83
7 Jane F 12 59.8 84.5
8 Janet F 15 62.5 112.5
9 Jeffrey M 13 62.5 84
10 John M 12 59 99.5
11 Joyce F 11 51.3 50.5
12 Judy F 14 64.3 90
13 Louise F 12 56.3 77
14 Mary F 15 66.5 112
15 Philip M 16 72 150
16 Robert M 12 64.8 128
17 Ronald M 15 67 133
18 Thomas M 11 57.5 85
19 William M 15 66.5 112
WANT SAS dataset with 13 year olds;
====================================
Up to 40 obs from class total obs=3
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alice F 13 56.5 84
2 Barbara F 13 65.3 98
3 Jeffrey M 13 62.5 84
* read access table andd create SAS dataset;
* had to use the 32bit driver and R32 bit;
%utl_submit_r32("
source('c:/Program Files/R/R-3.3.2/etc/Rprofile.site',echo=T);
Sys.setenv(JAVA_HOME='C:\\Program Files (x86)\\Java\\jre7');
library(RODBC);
library(foreign);
myDB <- odbcConnectAccess('d:/mdb/demo.mdb',uid='admin',pwd='');
sqlTables(myDB);
class<- sqlQuery(myDB, paste('select * from class where AGE=13'));
write.dta(class,convert.factors='string',version = 10L, 'd:/dta/class.dta')
");
filename imp 'd:/dta/class.dta' lrecl=32756;
proc import
out=work.class
file=imp
dbms=DTA replace;
run;
proc print data=class;
run;quit;
Up to 40 obs from class total obs=3
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alice F 13 56.5 84
2 Barbara F 13 65.3 98
3 Jeffrey M 13 62.5 84
Hi SAS KIWI,
I tried Proc Setinit, and could see the following,
SAS/ACCESS Interface to Oracle
SAS/ACCESS Interface to PC Files
SAS/ACCESS Interface to Teradata
So i guess these products are there.
Proc Setinit shows you what's licensed but that doesn't mean it has also been properly installed and configured.
Proc Product_Status will show you what's installed.
http://blogs.sas.com/content/sgf/2012/11/12/how-to-find-your-sas-version-and-components-list/
Hi Patrick,
Thanks for the reply.
I checked PROC PRODUCT_STATUS, and i could see ACCESS is installed
For SAS/ACCESS Interface to Oracle with version info .
Regards,Aruna
To connect to MS Access you need either SAS/ACCESS to PC Files or SAS/ACCESS to ODBC installed.
From what you write, you've got only SAS/ACCESS to Oracle installed which allows you to connect to an Oracle Database.
N.B: The word Access in the SAS/Access modules has nothing to do with MS Access. That's just the naming convention SAS uses for its modules.
Hi Patrick,
I have SAS/ACCESS to PC files, listed
thanks,
Aruna 🙂
Hi Guys,
Spoke to IT and found that my SAS EG is 64 bit and ACCESS is 32 bit 😞
searched and used this piece of code but dint worked,
proc import dbms=accesscs
out=A1 replace
table='T_inven';
port=8561;
server="SASA";
database='I:\ANALYTICS\ Database_PROD.accdb';
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.