Connecting QuerySurge to SAS Data Sets (SAS7DBAT files) with the CData JDBC Driver

Versions: 8.0+

SAS7BDAT files, or binary-dataset created by SAS statistical analysis software suite. SAS7BDAT format was introduced in SAS version 7.  A SAS data set is a SAS file stored in a SAS library that SAS creates and processes. The file is mainly used to store analytics, forecast statistics, and other business intelligence data. In this article, we show the connection setup for the CData SAS Data Sets JDBC driver.

Note: RTTS, the vendor of QuerySurge, partners with CData to make a broad range of JDBC drivers available to QuerySurge users. For information about our partnership, click here. See all of CData's JDBC offerings here. For questions related to ordering, contact us here.

For a SAS Data Sets CData Connection, you will need the following information:

• Directory path where the CData SAS Data Sets files (.sas7dbat) are located
• The file extension should be included on all SAS Data Sets files. (.sas7dbat)

 Note: The directory specified should only contain SAS7DBAT files. The presence of other file types can cause unwanted metadata issues and behavior when using the Connection in the QueryWizard.

Adding a SAS Data Sets Data Source Connection using the CData JDBC Driver

To connect to a SAS Data Sets data source, you'll need to use the Connection Extensibility feature of the QuerySurge Connection Wizard.

1. Download the SAS Data Sets JDBC driver from CData.
   
   
2. Deploy the SAS Data Sets JDBC driver to your Agent(s). To install the CData driver, unzip the driver download and run their driver installer (setup.jar) to install the driver on your Agent box(es) (instructions for doing so can be found in the readme.txt file). We recommend that you install the driver in the default location. Once installed, copy the driver file(s) and the license file from the installation directory to your QuerySurge Agent jdbc directory (see the relevant Knowledge Base article for deploying drivers to Agents on Windows or Agents on Linux). You'll need to deploy a driver for each Agent box on which you intend to use the driver. 
   

   Note: For this example the deployed files are: a) cdata.jdbc.sasdatasets.jar and b)  cdata.jdbc.sasdatasets.lic.
   
   

3. Login to QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Select Connections in the left nav tree, and click the Add button (at the bottom left of the main panel). Leave the Advanced Mode checkbox unchecked. You'll need the following standard information for the next few steps.
   

   Driver Class: cdata.jdbc.sasdatasets.SASDataSetsDriver

   Connection URL template: jdbc:sasdatasets:URI=<Folder Path>
   

   The SAS Data Sets JDBC driver connects to a folder. Each agent you wish to use the SAS Data Sets connection needs to be able to access the folder. Replace <Folder Path> with your folder path.
   
   
4. Enter a Connection Name, and select * All Other JDBC Connections (Connection Extensibility) from the Data Source dropdown menu.
   
   
   
5. Enter the Driver Class.
   
   
   
6. Enter the Connection URL. 
   
   You can enter an optional Test Query. The table name, in this case, is a CData driver system table that should return if the connection is configured correctly.
   
   
7. Verify the connection details, and optionally test the connection before saving.
   

You're all set! You can write and execute QueryPairs against SAS Data Sets. The CData documentation discusses different driver configuration options, advanced settings, and supported SQL syntax and functions.