Automate SAS xpt Integration Tasks from PowerShell

The CData Cmdlets for SAS xpt are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to SAS xpt.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the SAS xpt API, but also an SQL interface; this tutorial shows how to use both to retrieve SAS xpt data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for SAS xpt. To access SAS xpt data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for SAS xpt.

After obtaining the needed connection properties, accessing SAS xpt data in PowerShell consists of three basic steps.

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).

PowerShell

1. Install the module:

   Install-Module SASXptCmdlets

2. Connect:

   $sasxpt = Connect-SASXpt -URI "$URI"

3. Search for and retrieve data:

   $column2 = "100" $sampletable_1 = Select-SASXpt -Connection $sasxpt -Table "SampleTable_1" -Where "Column2 = `'$Column2`'" $sampletable_1

   You can also use the Invoke-SASXpt cmdlet to execute SQL commands:

   $sampletable_1 = Invoke-SASXpt -Connection $sasxpt -Query 'SELECT * FROM SampleTable_1 WHERE Column2 = @Column2' -Params @{'@Column2'='100'}

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for SAS xpt\lib\System.Data.CData.SASXpt.dll")

2. Connect to SAS xpt:

   $conn= New-Object System.Data.CData.SASXpt.SASXptConnection("URI=C:/folder;") $conn.Open()

3. Instantiate the SASXptDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Id, Column1 from SampleTable_1" $da= New-Object System.Data.CData.SASXpt.SASXptDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.id $_.column1 }