November 9, 2012 at 11:45 am
Greetings all. SAS (analytical software) comes with 4 data providers which conform to the Microsoft OLE DB standard. I was able to open and read a SAS dataset (.sas7bdat) from Excel with the following...
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
With cn
.Provider = "sas.LocalProvider"
.Properties("Data Source") = "c:\Imports"
.Open
End With
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.Open "pilotdata", cn, adOpenStatic, adLockReadOnly, adCmdTableDirect
If this works, I am thinking I could also use OPENROWSET, and indeed the provider appears in the list of providers under linked servers. I have tried everything in every possible combination I could think of to no avail, I just can't figure out the syntax. I know it will be impossible to test without having SAS installed on your machine, but I know some of you are VB gurus as well as TSQL, and I'm hoping someone will be able to translate my VB into OPENROWSET syntax. Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 9, 2012 at 12:07 pm
Maybe this will help. I was able to use the sas.LocalProvider with the data import wizard by supplying only 'c:imports' as the data source, and 'pilotdata' as the location in the data link properties dialog. Is there a way to view the code that is being generated?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply