Build your own SAS data set viewer using PowerShell

12

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.

(It has a TXT file extension for security purposes. To use it, save the script as a PS1 file on your local machine.)

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.

Share.

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. He's also co-author of the popular SAS for Dummies book, author of Custom Tasks for SAS Enterprise Guide using Microsoft .NET, and a frequent participant on the SAS Enterprise Guide discussion forum.

12 Comments

  1. Hi Chris

    Thanks for all your posts - even I can make sense/use of them. Could you please do a post (or is there one already) on the query features of EG 5.1 - I can't get my head around resultset labels vs dataset names, and the new identifiers, the renaming of summarised variables (summarised computed columns are better)

  2. Hey Chris,
    Great stuff! Question...Is it possible to use this to connect to my remote Unix server, send SAS commands and pull the data set back if you I do not have local sas on your PC?

  3. Pingback: Using Windows PowerShell to view your SAS data dictionary

  4. Pingback: Using Windows PowerShell to connect to a SAS Metadata Server - The SAS Dummy

  5. Pingback: Using Windows PowerShell to find registered tables and columns in SAS metadata - The SAS Dummy

  6. Pingback: My favorite SAS tips and tools from 2012 - The SAS Dummy

Leave A Reply

Back to Top