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:
- Copy the program into a local file (for example, c:\datatest\ShowColumns.vbs).
- Change the filename and path variables in the script to point to data files on your machine.
- Open a command prompt window.
- 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
6 Comments
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
Chris,
Thanks for this useful script.Can i open SAS Cport Transport file by using Local Provider,Can you help on this
I don't think the Local provider can read CPORT files. I think it can deal with XPORT files if you have them though.
Pingback: Closing the “LIBNAME loophole” with metadata-bound libraries - The SAS Dummy
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.
Yes, I think it can be done. I have a more complete example of viewing/exporting SAS data using Windows PowerShell. PowerShell can also be used to access SQL Server, so you should be able to use the scripting tool as a way to transport data from one form to another.