Desktop productivity for business analysts and programmers

Performance warning when opening data using ODBC

Reply
Contributor
Posts: 61

Performance warning when opening data using ODBC

I am using SAS Enterprise Guide Version 7.12 HF1.

 

I would like to load data from a table in an Sql Server using an ODBC connection that I have created. When I click on File > Open > ODBC, I get the following warning:

Capture.PNG

I surely want to load all the data from a table without any limit but apparently I am not using SAS/ACESS (which is the preferred way) to do so. How can I make sure I am loading my data using SAS/ACCESS instead of a direct connection.

Trusted Advisor
Posts: 1,052

Re: Performance warning when opening data using ODBC

Hi, Pedram

 

SAS/Access is used when you access data from SAS code. So instead of using the "File Open" dialog, you would have code something like this:

 

data mydata;

set ODBCdata.database_table;

run;

 

where ODBCdata has been configured as a library accessing your sql Sql Server source.

 

First, make sure you're licensed for SAS/Access for ODBC. Then, you'll need to talk to your SAS Admin about getting it configured properly.

 

Tom

Contributor
Posts: 61

Re: Performance warning when opening data using ODBC

I'm sorry but please could you be more specific?

Our license includes SAS/ACESS. Is it possible to connect using SAS/ACESS to Enterprise Guide?

If so, how? A very brief example will do.

 

Let's say the SQL connection string to my database is MY_CONNECTION_STRING. And I would like to create a SAS dataset from all the rows that exists in the table MY_TABLE. If it is not that straightforward that I can contact my SAS Admin.

Community Manager
Posts: 2,693

Re: Performance warning when opening data using ODBC

You have to define the data source in terms of the SAS server (connecting your SAS session to the database) instead of opening directly from SAS Enterprise Guide.

 

Typically, you would have a libname statement like:

 

libname db odbc dsn='mydata' user='user' password='pw';

Where the DSN is references a defined source that you've configured on your server machine.  Or, for SQL Server, you might use LIBNAME OLEDB with a connection string -- again, defined in terms of your SAS server.  

 

The File->Open->ODBC method works as a convenience, but it pipes all data through your EG client to the SAS server, a two-hop process that can be slow and is not repeatable in a SAS program node.

Contributor
Posts: 61

Re: Performance warning when opening data using ODBC

Where should I mention the line above?

Trusted Advisor
Posts: 1,052

Re: Performance warning when opening data using ODBC

How are you using the data you bring in from SQL Server? Are you using it in one of the EG "tasks", or are you writing SAS code in a code window?

 

If a task, which task?

 

Tom

Contributor
Posts: 61

Re: Performance warning when opening data using ODBC

I use File > Open > ODBC to open the data from SQL Server.

Trusted Advisor
Posts: 1,052

Re: Performance warning when opening data using ODBC

Yes, but what do you do with the data once you have it open?

Community Manager
Posts: 2,693

Re: Performance warning when opening data using ODBC

Experiment with the LIBNAME statement in a new Program window.  Here's a SAS Note that might help you learn how to define the connection

 

When you have it working, it's probably best to ask a system administrator to define the DSN on the SAS server machine, and if the library has to be referenced by lots of users, define it in an autoexec or in metadata, as suggested by @LinusH.

 

For ODBC, you need SAS/ACCESS to ODBC.  For OLE DB, you need SAS/ACCESS to OLE DB (Windows) or SAS/ACCESS to SQL Server (Unix).  If working with SAS on Unix, you'll definitely need the help of an admin to configure the data connection definitions.

Contributor
Posts: 61

Re: Performance warning when opening data using ODBC

I will do so. Thanks. So basically EG may not be the best tool here. I would just have to write the SAS Base code. I am new to EG and my impression was that it facilitates all SAS processes including building datasets from ODBC and scheduling them to be copied to other locations.

Trusted Advisor
Posts: 1,052

Re: Performance warning when opening data using ODBC

Hi, Pedram

 

You mention that you are new to EG. Are you new to SAS as well, or are you experienced with writing SAS code?

Community Manager
Posts: 2,693

Re: Performance warning when opening data using ODBC

EG certainly does facilitate a lot once you have your data access established.  Beyond the simple LIBNAME (to establish the database connection), you might not need to write much code on your own.  The Query Builder and other tools can take over after that.

 

Still, with databases it's good to be careful, as you don't want to create a nonperformant query that hobbles the database.

Esteemed Advisor
Posts: 5,198

Re: Performance warning when opening data using ODBC

If you are using a SAS server talk to the SAS server admin to help you define the libref. Ideally it should be registered in Metadata to potentially benefit other users.
Data never sleeps
Ask a Question
Discussion stats
  • 12 replies
  • 522 views
  • 2 likes
  • 4 in conversation