Automate SAS Data Sets Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

SAS Data Sets ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with SAS Data Sets.



Are you looking for a quick and easy way to access SAS Data Sets data from PowerShell? We show how to use the Cmdlets for SAS Data Sets and the CData ADO.NET Provider for SAS Data Sets to connect to SAS Data Sets data and synchronize, automate, download, and more.

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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the SAS Data Sets API, 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.

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

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

  • URI: Set this to the folder containing your .sas7bdat resources. Currently we only support local files.

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 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 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 System.Data.CData.SASDataSets.SASDataSetsParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()