09-05-2016 07:22 AM
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.
09-05-2016 08:07 AM
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:
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.
09-05-2016 08:20 AM
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.
09-05-2016 09:00 AM
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.
09-05-2016 10:14 AM
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?
09-05-2016 10:40 AM
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.
09-06-2016 03:15 AM
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.
09-06-2016 07:14 AM
You mention that you are new to EG. Are you new to SAS as well, or are you experienced with writing SAS code?
09-06-2016 08:31 AM
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.
09-05-2016 10:25 AM