BookmarkSubscribeRSS Feed
SAS_INFO
Quartz | Level 8

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

8 REPLIES 8
SASKiwi
PROC Star

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. 

rogerjdeangelis
Barite | Level 11
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

SAS_INFO
Quartz | Level 8

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.

Patrick
Opal | Level 21

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/

 

SAS_INFO
Quartz | Level 8

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

Patrick
Opal | Level 21

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.

SAS_INFO
Quartz | Level 8

Hi Patrick,

 

I have SAS/ACCESS to PC files, listed

 

thanks,

Aruna 🙂

SAS_INFO
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 8 replies
  • 1502 views
  • 0 likes
  • 4 in conversation