Peek at your data using VBScript, OLE DB, and the SAS local data provider

Let’s pretend for a moment that you don’t have SAS installed on all of your office computers. But you’ve got some great content locked away inside SAS data sets. Is there a way to get to the content of that data, without having to push the data through a SAS tier?

There is a way! All that you need is the SAS OLE DB Local Data Provider and some programming know-how. You can download the provider from SAS. And the know-how? The basic recipes are in the SAS 9.2 Providers for OLE DB Cookbook. (There’s a version for SAS 9.1 as well.)

Note: there are limitations when using the Local Data Provider. Because there is no SAS session in this mix, you don’t get the benefit of SAS data services. For example, it can’t handle your user-defined formats. You might sacrifice precision for some numeric values. You don’t have the full cross-platform support that SAS provides.

Here is an example script that verifies that the provider is installed, opens a data set file, and reports on the row count and column names/types. It’s a very simple example that doesn’t get very fancy and doesn’t include proper error checking, but I hope that it shows you the possibilities.

To run the example on your Windows machine:

1. Copy the program into a local file (for example, c:\datatest\ShowColumns.vbs).
2. Change the filename and path variables in the script to point to data files on your machine.
3. Open a command prompt window.
4. Run the example program with cscript.exe. For example:
   cscript.exe c:\datatest\ShowColumns.vbs
    

   Note that this can work with the 32-bit or 64-bit versions of the SAS OLE DB providers. If you are on a 64-bit machine and you want to use the 32-bit provider, be sure to run the 32-bit version of cscript.exe:

   %windir%\SysWow64\cscript.exe c:\datatest\ShowColumns.vbs
    

Here’s the program:

Output from my example: