Automate SAS Data Sets Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

Once you have acquired the necessary connection properties, accessing SAS Data Sets data in PowerShell can be enabled in three steps.

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.

PowerShell

1. Install the module:

   Install-Module SASDataSetsCmdlets

2. Connect:

   $sasdatasets = Connect-SASDataSets -URI "$URI"
   

3. Search for and retrieve data:

   $cuisine = "American"
   $restaurants = Select-SASDataSets -Connection $sasdatasets -Table "restaurants" -Where "cuisine = `'$cuisine`'"
   $restaurants
   

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

   $restaurants = Invoke-SASDataSets -Connection $sasdatasets -Query 'SELECT * FROM restaurants WHERE cuisine = @cuisine' -Params @{'@cuisine'='American'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to SAS Data Sets:

    
   $conn= New-Object System.Data.CData.SASDataSets.SASDataSetsConnection("URI=C:/myfolder;")
   $conn.Open()
   

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

   $sql="SELECT name, borough from restaurants"
   
   $da= New-Object System.Data.CData.SASDataSets.SASDataSetsDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.name $_.borough
   }
    

Update SAS Data Sets Data

PowerShell

Update-SASDataSets -Connection $SASDataSets -Columns @('name','borough') -Values @('Myname', 'Myborough') -Table restaurants -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SASDataSets.SASDataSetsCommand("UPDATE restaurants SET cuisine='American' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SASDataSets.SASDataSetsParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert SAS Data Sets Data

PowerShell

Add-SASDataSets -Connection $SASDataSets -Table restaurants -Columns @("name", "borough") -Values @("Myname", "Myborough") 

ADO.NET

$cmd = New-Object System.Data.CData.SASDataSets.SASDataSetsCommand("INSERT INTO restaurants (cuisine) VALUES (@mycuisine)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SASDataSets.SASDataSetsParameter("@mycuisine","American")))
$cmd.ExecuteNonQuery()

Delete SAS Data Sets Data

PowerShell

Remove-SASDataSets -Connection $SASDataSets -Table "restaurants" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SASDataSets.SASDataSetsCommand("DELETE FROM restaurants WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SASDataSets.SASDataSetsParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject