BookmarkSubscribeRSS Feed
EdC
Calcite | Level 5 EdC
Calcite | Level 5

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.

5 REPLIES 5
LinusH
Tourmaline | Level 20

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).

Data never sleeps
EdC
Calcite | Level 5 EdC
Calcite | Level 5

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,

 

 

LinusH
Tourmaline | Level 20

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.

Data never sleeps
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Kurt_Bremser
Super User

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.

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
  • 5 replies
  • 2330 views
  • 1 like
  • 4 in conversation