SAS Access to Salesforce, SQL Server, Oracle and RedShift using ODBC Driver

In this tutorial we will show you how to connect from SAS to Salesforce, SQL Server, Oracle or RedShift using our DataDirect Connect for ODBC drivers. The steps that I am using here can easily be applied to any of the other data sources that we support.

1. Install the Drivers:
   Install DataDirect Connect for ODBC 32 or 64bit driver on your Linux client. Ensure that the bits of the driver match the bits of your SAS application. You can see the following video to install DataDirect Connect for ODBC Drivers.
2. Set your environment variables:

   To help you perform this task, DataDirect provides shell scripts for both the Bourne (odbc.sh) and C (odbc.csh) shells. You’ll find these scripts in your installation directory.

   These scripts configure the following environment variables:

   LD_LIBRARY_PATH
   PATH
   ODBCINI
   ODBCINST

   If using the on-premise Salesforce, Oracle Service Cloud, or MongoDB drivers you will need to have a Java SE 6 or higher installed on your Linux client and on your LD_LIBRARY_PATH.

3. Test load the driver:

   Use the DataDirect-provided ivtestlib (or ddtestlib for 64bit) utility to test load the driver.

   You'll find this utility in the bin sub-directory of your installation. You'll need to provide it with the path to the driver as follows:

   ivtestlib /opt/progress/datadirect/lib/ivora27.so
   
   

   If your installation was successful and your environment variables are properly set, you should receive a message similar to the following:

   Load of /opt/progress/datadirect/lib/ivora27.so successful, qehandle is 0x944F030
   File version: 07.01.0157 (B0108, U0075)
   
   

   If you receive an error message at this point, there's a good chance that your environment variables aren't properly set.

4. Configure a connection:

   In order to configure a connection to your database, you will want to edit the odbc.ini file (found in your installation directory).

   Here is an example of my connection information for the DataDirect Connect for ODBC Salesforce, SQL Server, Oracle, and RedShift databases:

   
   
   
   
   

   Note: Ensure that the ODBC Data Sources section of your odbc.ini file lists out the data sources you have configured. For example:

   
5. Test your connection:

After entering your connection details into your odbc.ini file you can test your connection using the provided example tool. The example tool is located in the samples/example folder.

If you’re unable to successfully connect, double check your login settings with your database administrator. You can also search our Knowledge Base for the error message you received: http://knowledgebase.progress.com/

1. Install the required SAS products:

   Now that you have a working connection to your database, it’s time to install the necessary SAS software. In this example, I installed the SAS Foundation 9.4 along with the SAS/ACCESS interface to ODBC

2. Launch SAS Foundation: 

   When you install SAS Foundation, you're prompted to provide an installation directory. I chose /SASHome. SAS then created a subdirectory called SASFoundation/9.4, which is where you'll find the SAS command that launches the product. If you're unable to locate the SAS installation, contact your SAS administrator for more details.

   Note: ensure that the environment variables previously set are still set when you execute the SAS application

3. Run a basic SAS query:

   Once SAS Foundation is launched, you can access its query capabilities by switching into the SAS Log window and choosing Tools -> Query:

   

This launches the SAS: Query dialog. Once this is running, choose the Tools -> Switch Access Mode -> ODBC menu option:

SAS now displays a dialog box where you can enter in the data source, username, and password details that you configured earlier.

Once you press OK, SAS will attempt to connect, via the SAS/ACCESS interface to ODBC and the DataDirect driver to your source. If everything is configured correctly, you should see a list of all available tables. From here you can select the table (or tables) from the list:

You can then select any columns you want to include in your query and then choose the Tools -> Run Query -> Run Immediate to launch your query:

This will return results either to your SAS output window or to a web browser – depending on how you have your SAS Foundation configured:

 

Installing SAS and running a query will remain the same for other ODBC drivers including SQL Server, Oracle and RedShift. For more information, please visit our SAS solution page.