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.

(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.

tags: data sets, PowerShell

6 Comments

  1. Andreas
    Posted April 13, 2012 at 7:57 am | Permalink

    Very nice post. Thanks for sharing your knowledge.

  2. Bob
    Posted April 13, 2012 at 9:33 am | Permalink

    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)

  3. Jonathan
    Posted April 13, 2012 at 10:45 am | Permalink

    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?

    • Chris Hemedinger Chris Hemedinger
      Posted April 13, 2012 at 10:49 am | Permalink

      Jonathan,
      Yes - all possible! Those examples are coming...

  4. Posted April 13, 2012 at 10:57 am | Permalink

    Yeah!!! A PowerShell tip with SAS.

    This is great Chris. I haven't played a lot with PS so this is a nice cut-and-paste article to help with the SAS side.

  5. Posted October 20, 2013 at 11:02 am | Permalink

    NOTE: there is also a commandlet called "Read-SasData" available to query SAS datasets at https://sourceforge.net/p/cmdlets4sas/

4 Trackbacks

  1. [...] a previous post I showed how you can use Windows PowerShell (with the SAS Local Data Provider) to create a SAS data [...]

  2. [...] PowerShell to find registered tables and columns in SAS metadata Running Windows PowerShell scripts Build your own SAS data set viewer using Windows PowerShell Example of using Windows PowerShell to query SAS table metadata from SAS Metadata Server (GitHub) [...]

  3. [...] Windows PowerShell to connect to a SAS Metadata Server Running Windows PowerShell scripts Build your own SAS data set viewer using Windows PowerShell Example of using Windows PowerShell to query SAS table metadata from SAS Metadata Server (GitHub) [...]

  4. [...] Build your own SAS data set viewer using PowerShell [...]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>