11-02-2015 06:59 AM
I am trying to connect SAS from VBA and fetch the data to excel spread sheet. Below is the code what I am trying.
Public Sub GetData()
Dim obConnection As ADODB.Connection
Dim obRecordset As ADODB.Recordset
Dim i As Integer
Set obConnection = New ADODB.Connection
obConnection.Provider = "sas.LocalProvider.1"
obConnection.Properties("Data Source") = "sas.abc.abc.com:1234"
obConnection.ConnectionString = "Provider=SAS.IOMProvider; Data Source=iom-bridge://sas.abc.abc.com:1234; User ID=abcd;Password=1234"
Set obRecordset = New ADODB.Recordset
obRecordset.Open "proc sql; select * from aa.getdata_dim; QUIt;", obConnection, adOpenStatic, adLockPessimistic,
When my code try to open the recordset. Code fails and I am prompted with run time error as below.
Microsoft Visual Basic for Applications
Run-time error '3709':
Application-defined or object-defined error
Has any one achieved this, and help me out ?
11-02-2015 07:12 AM
Yes, I don't think you can do it like that. Datasets are not a database. Also, you seem to be trying to use SAS commmands to select??
What is wrong with just saving the data to CSV, then you can open the CSV file via VBA.
11-02-2015 07:15 AM
My intension is to build a connection string with SAS and get the data in the spreadsheet. Later on I am using those data to make some formation. I am sure, we can do this. but i am facing some difficulties. So some one must have for sure tried this out.
11-02-2015 07:58 AM
Actually yes, a quick google search results in:
However I would still question why you want to do this. Just export the data from SAS to CSV, SAS is not a database, hence you wouldn't want to be querying it at regular intervals like you do with a database. When you want to use the data, run an export. Reasons this would be a better approach - formats would not be pushed out properly and you may want to process the data before export.
If you want access to data in the form of a database, use a database?
11-02-2015 08:13 AM
11-02-2015 08:36 AM
Well, I would still use one or the other.
As for that code, it looks like the first instance is referencing connection information as stored in a file on your local machine, whereas the second appears to be contacting a web address, however it looks like the web address you have given is just a dummy one to give an example, i.e. you would change to the URL where the connection is, and apparently a post number after the web address.
You may be better off checking a ticket with the helpdesk, I don't know of anyone who tries to pull data out of a dataset with VBA, its always create an export file then import. What is it you need to automate, there may be better options.