Desktop productivity for business analysts and programmers

SAS Access Connections

Reply
Contributor
Posts: 63

SAS Access Connections

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

Super User
Posts: 3,240

Re: SAS Access Connections

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. 

Valued Guide
Posts: 505

Re: SAS Access Connections

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

Contributor
Posts: 63

Re: SAS Access Connections

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.

Respected Advisor
Posts: 4,138

Re: SAS Access Connections

[ Edited ]

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/

 

Contributor
Posts: 63

Re: SAS Access Connections

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

Respected Advisor
Posts: 4,138

Re: SAS Access Connections

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.

Contributor
Posts: 63

Re: SAS Access Connections

Hi Patrick,

 

I have SAS/ACCESS to PC files, listed

 

thanks,

Aruna Smiley Happy

Contributor
Posts: 63

Re: SAS Access Connections

Hi Guys,

 

Spoke to IT and found that my SAS EG is 64 bit and ACCESS is 32 bit Smiley Sad

 

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;

Ask a Question
Discussion stats
  • 8 replies
  • 236 views
  • 0 likes
  • 4 in conversation