Excel Spreadsheet Automation on SAS xpt Data with the QUERY Formula

The CData Excel Add-In for SAS xpt provides formulas that can query SAS xpt data. The following three steps show how you can automate the following task: Search SAS xpt 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 xpt data records you want to retrieve, written in standard SQL.

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

  Connecting to Local SASXpt Files

  You can connect to local SASXpt file by setting the URI to a folder containing SASXpt files.

  Connecting to S3 data source

  You can connect to Amazon S3 source to read SASXpt files. Set the following properties to connect:

  • URI: Set this to the folder within your bucket that you would like to connect to.
  • AWSAccessKey: Set this to your AWS account access key.
  • AWSSecretKey: Set this to your AWS account secret key.
  • TemporaryLocalFolder: Set this to the path, or URI, to the folder that is used to temporarily download SASXpt file(s).

  Connecting to Azure Data Lake Storage Gen2

  You can connect to ADLS Gen2 to read SASXpt files. Set the following properties to connect:

  • URI: Set this to the name of the file system and the name of the folder which contacts your SASXpt files.
  • AzureAccount: Set this to the name of the Azure Data Lake storage account.
  • AzureAccessKey: Set this to our Azure DataLakeStore Gen 2 storage account access key.
  • TemporaryLocalFolder: Set this to the path, or URI, to the folder that is used to temporarily download SASXpt file(s).
• 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 xpt data, such as Column2.
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 SampleTable_1 WHERE Column2 = '"&B2&"'","URI="&B1&";Provider=SASXpt",B3)

👁 Formula inputs used in this example. (Google Apps is shown.)

3. Change the filter to change the data. 👁 The outputs of the formula. (Google Apps is shown.)