Blogs

Blogs

Build your own SAS data set viewer using PowerShell

16
By

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.

Tags data sets PowerShell
Share

About Author

Chris Hemedinger

Director, SAS User Engagement

+Chris Hemedinger is the Director of SAS User Engagement, which includes our SAS Communities and SAS User Groups. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies. 

16 Comments

  2. Bob on

    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 on

    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?

  4. Alan Churchill on

    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. Pingback: Using Windows PowerShell to view your SAS data dictionary

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

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

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

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

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

  13. Christina on

    Hi Chris, thank you for this! Does it work when there is missing data in sas data set? I asked because I tried python Read_sas and it does not work when there is missing data or for very big dataset.

    • Chris Hemedinger
      Chris Hemedinger on

      Yes, it should. It makes use of the SAS Local Data Provider for OLE DB, and that is basically the SAS engine for reading data...but localized so it can't handle custom formats and other features that come with a full SAS environment.

Back to Top