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:
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.
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
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.
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.
Where should I mention the line above?
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
I use File > Open > ODBC to open the data from SQL Server.
Yes, but what do you do with the data once you have it open?
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.
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.
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.