Using Windows PowerShell to view your SAS data dictionary

6

In a previous post I showed how you can use Windows PowerShell (with the SAS Local Data Provider) to create a SAS data set viewer.  This approach doesn't require that you have SAS installed, and allows you to read or export the records within a SAS data set file.

In this post, I'll present two companion scripts that allow you to:

  • Gather the table-level metadata about a SAS data set, including the observation count, created and last-modified dates, data set label, and more.
  • Gather the column-level metadata within each SAS data set, including variable names, types, formats, lengths, and more.

If you make use of the SAS DICTIONARY tables (as seen in SASHELP.VMEMBER and SASHELP.VCOLUMN), these scripts will provide familiar information.  But like my previous example, these scripts do not require a SAS installation.

Why is this useful? Of course, the best way to read SAS data sets is to use SAS.  And if you have SAS data sets, the probability is high that you have SAS installed somewhere, so why not use it?  It turns out that even among companies that use SAS, not every employee has access to a SAS environment.  (Tragic, right?)  And since SAS data sets are often treated as a corporate asset (or, at least, the information within the data sets is), these are subject to cataloging and auditing by staff who don't use SAS.  These scripts can enable a light-weight auditing process with a minimum of installation/licensing complications.

Here are links to all three scripts.  To use them, save each file to your local PC as a .PS1 file.  You will also need to make sure that you can run Windows PowerShell scripts, and that you have the SAS OLE DB Local Data Provider installed (free to download).

The output of each of these scripts is in the form of PowerShell objects, which are most useful when piped into another PowerShell cmdlet such as Out-GridView (for visual display) or Export-CSV (for use as input to Excel or another data-driven process).

Examples (as run from a PowerShell console window)

To view the table information about all SAS data sets in a file path w:/ (including subfolders):

.\ReadSasDataTables.ps1 w:\ | Out-GridView

Result (click on the image to see the full-size version):

To export the SAS table information to a CSV file:

.\ReadSasDataTables.ps1 w:\ | Export-CSV -NoType -Path c:\report\tables.csv

Result:
"FileName","Path","FileTime","FileSize","TableName","Label","Created","Modified","LogicalRecords","PhysicalRecords","RecordLength","Compressed","Indexed","Type","Encoding","WindowsCodepage"
"users.sas7bdat","w:\","5/8/2012 9:37:03 AM","466944","users","","5/8/2012 9:37:03 AM","5/8/2012 9:37:03 AM","412","412","952","NO","False","","20","65001"
"bloglist.sas7bdat","w:\","5/8/2012 9:37:03 AM","73728","bloglist","","5/8/2012 9:37:03 AM","5/8/2012 9:37:03 AM","28","28","1360","NO","False","","20","65001"
"posts.sas7bdat","w:\","5/8/2012 9:37:09 AM","103555072","posts","","5/8/2012 9:37:07 AM","5/8/2012 9:37:07 AM","41077","41077","2496","NO","False","","20","65001"
"postviews.sas7bdat","w:\","5/8/2012 9:37:09 AM","5120000","postviews","","5/8/2012 9:37:09 AM","5/8/2012 9:37:09 AM","4808","4808","1040","NO","False","","20","65001"
"comments.sas7bdat","w:\","5/8/2012 9:37:12 AM","26943488","comments","","5/8/2012 9:37:11 AM","5/8/2012 9:37:11 AM","7807","7807","3432","NO","False","","20","65001"
"published_posts.sas7bdat","w:\","5/8/2012 9:37:13 AM","11739136","published_posts","","5/8/2012 9:37:13 AM","5/8/2012 9:37:13 AM","4628","4628","2512","NO","False","","20","65001"
"blogsocial.sas7bdat","w:\","5/6/2012 1:19:06 PM","401408","blogsocial","","5/6/2012 1:19:06 PM","5/6/2012 1:19:06 PM","682","682","544","NO","False","","20","65001"

To view the columns (variables) information for all SAS data sets within a folder w:/ (including subfolders):
.\ReadSasDataColumns.ps1 w:\ | Out-GridView

Result (click on the image to see the full-size version):

To export the SAS columns information to a CSV file:

.\ReadSasDataColumns.ps1 w:\ | Export-CSV -NoType -Path c:\report\columns.csv

Result (partial):
"File name","Column","Label","Pos","Type","Length","Format","Informat","Indexed","Path","File time","File size"
"users.sas7bdat","ID","ID","1","NUM","0","","","False","w:\","5/8/2012 9:37:03 AM","466944"
"users.sas7bdat","user_login","user_login","2","130","90","$180.","$180.","False","w:\","5/8/2012 9:37:03 AM","466944"
"users.sas7bdat","user_registered","user_registered","3","NUM","0","DATETIME19.","DATETIME19.","False","w:\","5/8/2012 9:37:03 AM","466944"
"users.sas7bdat","display_name","display_name","4","130","375","$750.","$750.","False","w:\","5/8/2012 9:37:03 AM","466944"
"bloglist.sas7bdat","blog_id","blog_id","1","NUM","0","","","False","w:\","5/8/2012 9:37:03 AM","73728"
"bloglist.sas7bdat","name","option_value","2","130","512","$1024.","$1024.","False","w:\","5/8/2012 9:37:03 AM","73728"
"bloglist.sas7bdat","path","path","3","130","150","$300.","$300.","False","w:\","5/8/2012 9:37:03 AM","73728"
"bloglist.sas7bdat","registered","registered","4","NUM","0","DATETIME19.","DATETIME19.","False","w:\","5/8/2012 9:37:03 AM","73728"
"bloglist.sas7bdat","last_updated","last_updated","5","NUM","0","DATETIME19.","DATETIME19.","False","w:\","5/8/2012 9:37:03 AM","73728"
"bloglist.sas7bdat","public","public","6","NUM","0","","","False","w:\","5/8/2012 9:37:03 AM","73728"

 

Share

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. 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.  He also hosts the SAS Tech Talk webcasts each year from SAS Global Forum, connecting viewers with smart people from SAS R&D and the impressive work that they do.

6 Comments

  1. Pingback: Build your own SAS data set viewer using PowerShell - The SAS Dummy

    • Chris Hemedinger
      Chris Hemedinger on

      Stats guy, I don't think there is an independent data provider for UNIX. However, if your data are on UNIX and you can connect to a UNIX SAS session via SAS Integration Technologies (as you might do with SAS Enterprise Guide), you can build a Windows-based viewer that gets to your UNIX data. I've got some examples and a paper at this sasCommunity.org page.

  2. Hi Chris. Is there a way to identify a 32bits or 64 bits SAS datasets with a PowerShell script like ReadSASdataTables.ps1)?
    Thanks.
    Alain
    Quebec City SAS User's Group

    • Chris Hemedinger
      Chris Hemedinger on

      Hi Alain, it's not 100% reliable, but you might be able to use binary reads to scan the data set file header and see the byte signature. You'll find signatures such as "9.0401M3X64_8PRO" from which you can infer the proper architecture.

Leave A Reply

Back to Top