topic SAS Access Connections in SAS Enterprise Guide

SAS Access Connections

SAS_INFO — Fri, 10 Feb 2017 05:25:12 GMT

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

Re: SAS Access Connections

SASKiwi — Fri, 10 Feb 2017 06:16:15 GMT

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.

Re: SAS Access Connections

rogerjdeangelis — Fri, 10 Feb 2017 22:59:30 GMT

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

Re: SAS Access Connections

SAS_INFO — Sun, 12 Feb 2017 23:04:14 GMT

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.

Re: SAS Access Connections

Patrick — Mon, 13 Feb 2017 05:49:28 GMT

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/

Re: SAS Access Connections

SAS_INFO — Mon, 13 Feb 2017 22:19:29 GMT

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

Re: SAS Access Connections

Patrick — Mon, 13 Feb 2017 22:26:46 GMT

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.

Re: SAS Access Connections

SAS_INFO — Tue, 14 Feb 2017 01:00:44 GMT

Hi Patrick,

I have SAS/ACCESS to PC files, listed

thanks,

Aruna 🙂

Re: SAS Access Connections

SAS_INFO — Tue, 14 Feb 2017 03:59:08 GMT

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;