topic Re: Local ODBC driver in SAS Enterprise Guide

Local ODBC driver

EdC — Fri, 15 Jan 2016 09:18:56 GMT

Hi,

I'm using Base 9.3 and EG 5.1

I don't have access to the Unix server that SAS is installed on.

I've installed an ODBC driver on my PC it can connect to the server I want, I just want to run passthrou with it and query the DB through SAS / EG.

The closest I've come is to go file > open > ODBC and view all my tables and download them ,which isn't workable because they're huge tables and I just want to query and generate reports.

Any help would be good.

Re: Local ODBC driver

LinusH — Fri, 15 Jan 2016 09:57:03 GMT

So why download them? Query/report on the tables directly.

If that isn't feasible due to required logic/report constraints, you should require access to the SAS server instead (have EG connect to a SAS Workspace server on UNIX, and connect to your ODBC source from there instead).

Re: Local ODBC driver

EdC — Fri, 15 Jan 2016 10:17:28 GMT

How do I use the File > Open > ODBC option without downloading the dataset? Have I missed an option?

"(have EG connect to a SAS Workspace server on UNIX, and connect to your ODBC source from there instead)."

Do you have the syntax to do the above? I can only specify the drivers on the unix server.

Many thanks,

Re: Local ODBC driver

LinusH — Fri, 15 Jan 2016 11:10:28 GMT

Actually, I haven't used local ODBC drivers from EG much, but I assumed that by adding/opening them, does not necessarily mean that they are downloaded to the local PC, but I can be wrong.

If using a SAS server, you need first a login to the server, primarily a metadata user. The SAS server admin will help you with that.

To access the ODBC data from UNIX may require some server configuration (and license), if not already been done. Again, check with your server admin.

Then, best practice is to register your ODBC server and data in SAS metadata. By doing so, the connection should be available to yo "automatically". If you are responsible to do the connection yourselft, see SAS online doc for SAS/ACCESS to ODBC for syntax and examples. See LIBNAME and/or PROC SQL pass-thru.

Re: Local ODBC driver

ChrisHemedinger — Fri, 15 Jan 2016 12:55:27 GMT

It sounds like you don't have a way to access the database from your SAS server, so you are using EG (via ODBC) as a go-between. That process forces the entire table to pass through the client (your desktop) and back up to the SAS session, just so that you can then run SQL and other PROCs on it. It's very inefficient.

I wrote a primer on this that's available as a SAS Note: Efficient Data Access from SAS Enterprise Guide.

I also recorded a YouTube video -- many years ago -- that illustrates the concepts using our favorite type of data (M&Ms!).

If you have no way to get to the database from your UNIX SAS session, then the only other workaround I can suggest is perhaps create an ODBC view to the subset you need, perhaps via a local instance of Microsoft Access (if you have it). Then use the MS Access ODBC driver to access that and create the subset for you. This is a lot of layers of indirection, I know. The most efficient solution would be to use SAS/ACCESS to whatever database you've got, and let SAS push through the query to the database. But that's an additional SAS module and more configuration on your server.

Chris

Re: Local ODBC driver

Kurt_Bremser — Fri, 15 Jan 2016 13:14:32 GMT

If you need to access the DB from SAS regularly in your production environment, I highly recommend licensing and setting up SAS/ACCESS for the DBMS in question.

Depending on the frequency of access and required currentness of data, it might be an option to have the data unloaded from the DBMS to flat files and read those into SAS tables on the server (in batch jobs run by central production). This does not require additional licenses and the hassles of setting up the DB access.