BookmarkSubscribeRSS Feed
pedrammobedi
Quartz | Level 8

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.

12 REPLIES 12
TomKari
Onyx | Level 15

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

pedrammobedi
Quartz | Level 8

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.

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
pedrammobedi
Quartz | Level 8

Where should I mention the line above?

TomKari
Onyx | Level 15

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

pedrammobedi
Quartz | Level 8

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

TomKari
Onyx | Level 15

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

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
pedrammobedi
Quartz | Level 8

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.

TomKari
Onyx | Level 15

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?

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
LinusH
Tourmaline | Level 20
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

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
  • 12 replies
  • 1964 views
  • 2 likes
  • 4 in conversation