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

6

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:

' Change these to report on your data set
' Change path to the directory where your data set resides
' Change filename to the root name of the sas7bdat file (no extension)
path = "C:\Program Files\SAS\EnterpriseGuide\4.3\Sample\Data"
filename = "Candy_Sales_Summary" 
 
' Check registry for SAS Local Provider
Set WSHShell = CreateObject("WScript.Shell")
clsID = WSHShell.RegRead("HKCR\SAS.LocalProviderCLSID")
WScript.Echo "DIAGNOSTICS: SAS.LocalProvider CLSID is " & clsID
inProcServer = WSHShell.RegRead("HKCR\CLSID"  & clsID & "InprocServer32")
WScript.Echo "DIAGNOSTICS: Registered InprocServer32 DLL is " & inProcServer
 
' Constants for ADO calls
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adCmdTableDirect = 512
 
' Instantiate the provider object
Set obConnection = CreateObject("ADODB.Connection")
Set obRecordset = CreateObject("ADODB.Recordset")
 
obConnection.Provider = "SAS.LocalProvider"
obConnection.Properties("Data Source") = path
obConnection.Open
obRecordset.Open filename, obConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
 
'Report on Fields in this data set
WScript.Echo ""
WScript.Echo "Opened data " & filename & ", Record count: " & obRecordset.RecordCount
For Each Field In obRecordset.Fields
If Field.Type = 5 Then pType = "Numeric"
If Field.Type = 200 Then pType = "Character"
WScript.Echo Field.Name & " " & pType
Next
 
obRecordset.Close
obConnection.Close

Output from my example:

Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved. 

DIAGNOSTICS: SAS.LocalProvider CLSID is {0221264D-F909-4872-B7E6-B108D3FC8E8B}
DIAGNOSTICS: Registered InprocServer32 DLL is C:\Program Files\SAS\SharedFiles\SASProvidersForOLEDB\sasafloc0902.dll

Opened data Candy_Sales_Summary, Record count: 15000
Name Character
Region Character
OrderID Numeric
ProdID Numeric
Customer Numeric
Type Character
Product Character
Category Character
Subcategory Character
Retail_Price Numeric
Units Numeric
Discount Character
Date Numeric
Fiscal_Year Character
Fiscal_Quarter Character
Fiscal_Month_Num Numeric
Sale_Amount Numeric

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. Alan Churchill on

    Chris,

    Thanks for this very useful script. i can definitely use it.

    I made a minor adjustment so it takes the parms from the command-line:

    Call:

    cscript.exe x:temptest.vbs "x:temp" "class"

    Script:

    If Wscript.Arguments.Count < 2 Then
    WScript.Echo "Not enough arguments for processing."
    WScript.Quit
    End If

    path = WScript.Arguments(0)
    filename = WScript.Arguments(1)

    WScript.Echo "Path specified: " & path
    WScript.Echo "File name : " & filename

    ' Check registry for SAS Local Provider
    Set WSHShell = CreateObject("WScript.Shell")
    clsID = WSHShell.RegRead("HKCRSAS.LocalProviderCLSID")
    WScript.Echo "DIAGNOSTICS: SAS.LocalProvider CLSID is " & clsID
    inProcServer = WSHShell.RegRead("HKCRCLSID" & clsID & "InprocServer32")
    WScript.Echo "DIAGNOSTICS: Registered InprocServer32 DLL is " & inProcServer

    ' Constants for ADO calls
    Const adOpenDynamic = 2
    Const adLockOptimistic = 3
    Const adCmdTableDirect = 512

    ' Instantiate the provider object
    Set obConnection = CreateObject("ADODB.Connection")
    Set obRecordset = CreateObject("ADODB.Recordset")

    obConnection.Provider = "SAS.LocalProvider"
    obConnection.Properties("Data Source") = path
    obConnection.Open
    obRecordset.Open filename, obConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect

    'Report on Fields in this data set
    WScript.Echo ""
    WScript.Echo "Opened data " & filename & ", Record count: " & obRecordset.RecordCount
    For Each Field In obRecordset.Fields
    If Field.Type = 5 Then pType = "Numeric"
    If Field.Type = 200 Then pType = "Character"
    WScript.Echo Field.Name & " " & pType
    Next

    obRecordset.Close
    obConnection.Close

  2. Chris,

    Thanks for this useful script.Can i open SAS Cport Transport file by using Local Provider,Can you help on this

    • Chris Hemedinger
      Chris Hemedinger on

      I don't think the Local provider can read CPORT files. I think it can deal with XPORT files if you have them though.

  3. Pingback: Closing the “LIBNAME loophole” with metadata-bound libraries - The SAS Dummy

  4. Hi Chris,

    Your article is one of the few leads I found to access SAS without having it installed.

    Now, besides viewing the data, can you export a SAS table to SQL via this approach?

    I played around with the SQL DTS and was able to export it using the local provider, but I want to see if i can do that within my vb code.

    Thanks for looking at this.

Leave A Reply

Back to Top