SAS

Using Windows PowerShell to view your SAS data dictionary

by  •  • Comments Off on Using Windows PowerShell to view your SAS data dictionary

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.

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"

 

tags: data sets, ole db, PowerShell, PROC DATASETS

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.