Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for SAS Data Sets provides formulas that can edit, save, and delete SAS Data Sets data. The following three steps show how you can automate the following task: Search SAS Data Sets data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

• Query: The declaration of the SAS Data Sets data records you want to retrieve or the modifications to be made, written in standard SQL.

• Connection: Either the connection name, such as SASDataSetsConnection1, or a connection string. The connection string consists of the required properties for connecting to SAS Data Sets data, separated by semicolons.

  Set the following connection properties to connect to your SAS DataSet files:

  Connecting to Local Files

  • Set the Connection Type to "Local." Local files support SELECT, INSERT, and DELETE commands.
  • Set the URI to a folder containing SAS files, e.g. C:\PATH\TO\FOLDER\.

  Connecting to Cloud-Hosted SAS DataSet Files

  While the driver is capable of pulling data from SAS DataSet files hosted on a variety of cloud data stores, INSERT, UPDATE, and DELETE are not supported outside of local files in this driver.

  Set the Connection Type to the service hosting your SAS DataSet files. A unique prefix at the beginning of the URI connection property is used to identify the cloud data store and the remainder of the path is a relative path to the desired folder (one table per file) or single file (a single table). For more information, refer to the Getting Started section of the Help documentation.

• ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search SAS Data Sets data, such as cuisine.
2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
=CDATAQUERY("SELECT * FROM restaurants WHERE cuisine = '"&B2&"'","URI="&B1&";Provider=SASDataSets",B3)
3. Change the filter to change the data.