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.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to SAS xpt, 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 SASxpt. To access SAS xpt data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for SASxpt.

Once you have acquired the necessary connection properties, accessing SAS xpt data in PowerShell can be enabled in three 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 SASxpt\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
   }