Reading SAS data into Excel

[BruceBrad]

I have a SAS data set with two variables, ID and AMOUNT. The file is indexed on ID (no ID duplicates). I would like to write a VB function in Excel that would return the value of AMOUNT corresponding to a given ID. That is, like the VLOOKUP function in Excel, but reading data from the SAS dataset. I would probably have 200 or so lookups in a single spreadsheet, so the lookup would have to be reasonably efficient.

Which is the best technology to use for this? My thinking at the moment is to use ADO with the local data provider (that way I don't need to have a SAS session running). I'm concerned though that this might be too slow if my input dataset gets too large.

Is ADO the best strategy here?

[Bill_SAS · Accepted solution]

I can think of several ways to accomplish VLOOKUP, or VLOOKUP-like, functionality using SAS data. The one that comes to mind is:

1. Pull the data into Excel using either SAS Add-In for Microsoft Office or one of the SAS OLEDB data providers (Local Data Provider in your case).
2. Use the Excel VLOOKUP function against that now Excel resident table.
3. Use VB to refresh the Excel view of the SAS data as necessary.

An alternative would be to do a SQL query for each lookup value via ADO/OLEDB or even SAS Add-In for Microsoft Office. I think this would require more, and more complex, code writing but would remove the limitation of the data fitting into one worksheet (65536 row max) and having to refresh Excel's view of the data. For this, you are correct, ADO would be the best choice.

[BruceBrad]

Below is some ADO code that works for a small file at least. Not being very familiar with VB, I was surprised how easy it was. This needs to be entered as an Excel VB module, with a reference to the Microsoft ActiveX Data Objects Library activated.

Does the local data provider support the SEEK property (which would use an index to find the relevant record)?



Function SASLookup(ID, folder As String, sasfile As String)

Dim obConnection As New ADODB.Connection
Dim obRecordset As New ADODB.Recordset
obConnection.Provider = "sas.LocalProvider"
obConnection.Properties("Data Source") = folder
obConnection.Open
obRecordset.Open sasfile, obConnection, adOpenKeyset, adLockReadOnly, adCmdTableDirect

With obRecordset
.Filter = "ID='" & ID & "'"
If .EOF Then
SASLookup = "Not found"
ElseIf .RecordCount = -1 Then
SASLookup = "Cannot calculate number of records"
ElseIf .RecordCount <> 1 Then
SASLookup = "Not unique. Records = " & .RecordCount
Else
SASLookup = obRecordset.fields("value").Value
End If
.Close
End With
obConnection.Close
End Function

[deleted_user]

I'm a little confused here - so ignore me if I'm totally off track. But the equivalent of vlookup in Excel is format in SAS. Why not simply format ID to amount, and return the result?
Message was edited by: Pippen at May 22, 2006 7:37 PM
Got it the wrong way around!