Build your own SAS data set viewer using PowerShell

In about 30 lines of PowerShell script, we can build a SAS data set viewer that:

Does not require SAS on the PC

Provides very basic filtering capability

Also allows for easy export to CSV

All you need is the ability to run PowerShell scripts, and the SAS Local Data Provider for OLE DB.

I said that we could do this with about 30 lines of script. But I like to include some comments, error handling, and spacing for readability -- so the script has ballooned to about 120 lines.

Here's an example of how to run the script in PowerShell (put this all on one line):

ReadSasDataset.ps1 'C:\Data\westernrates.sas7bdat' |
Out-GridView -Title "Western Rates data"

Here's a screenshot of the result.

Suppose that you want to export the SAS data set to CSV, perhaps for use in Excel? Try this command (again, all on one line):

ReadSasDataset.ps1 'C:\Data\westernrates.sas7bdat' |
Export-CSV -Path C:\Data\WesternRates.csv -NoTypeInformation

Windows PowerShell has many built-in "cmdlets" (such as Export-CSV and the Out-GridView interface) that allow you to easily pipe output from one command to another, and shape the result into exactly what you need.

Download the full script: ReadSasDataset.ps1 (GitHub).

Here is the meat of the script. The script uses ADO to "connect" to the data set and read the records, one-by-one. For each field within each record, the script creates a new PowerShell psobject, which holds the "name-value pair" of column name and cell value. These are the actual output of the script. To do something useful with those objects, you pipe the results to another cmdlet.

Note: Those little backwards tickmarks that look like dirt specks on your screen are actually used as the PowerShell line continuation character. I used them here to make the script easier to read. Unlike the SAS language, PowerShell statements usually terminate with the end-of-line, not a semicolon.

$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset

$objConnection.Open("Provider=SAS.LocalProvider;Data Source=`"$filePath`";")
$objRecordset.ActiveConnection = $objConnection

# tell the SAS provider to return FORMATTED SAS values
$objRecordset.Properties.Item("SAS Formats").Value = "_ALL_"

# open the data set
# IMPORTANT: passing in a "missing" value for the connection
# because the connection is already on the RecordSet object
$objRecordset.Open($filename, [Type]::Missing,
$adOpenDynamic, `
$adLockOptimistic, `
$adCmdTableDirect)

$objRecordset.MoveFirst()

# read all of the records within the SAS data file
do
{
# build up a new object with the field values
$objectRecord = New-Object psobject

for ($i=0; $i -lt $objRecordset.Fields.Count; $i++)
{
# add static properties for each record
$objectRecord | add-member noteproperty `
-name $objRecordset.Fields.Item($i).Name `
-value $objRecordset.Fields.Item($i).Value;
}
# emit the object as output from this script
$objectRecord

# move on to the next record
$objRecordset.MoveNext()
}
until ($objRecordset.EOF -eq $True)

# close all of the connections
$objRecordset.Close()
$objConnection.Close()

In a future post, I'll provide more PowerShell examples that allow you to see all of the data set attributes for a collection of SAS data set files, similar to what you can see with PROC DATASETS.