BookmarkSubscribeRSS Feed
vps
Fluorite | Level 6 vps
Fluorite | Level 6

Hi - 

 

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, 
End Sub

 

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
---------------------------
OK Help
---------------------------

 

Has any one achieved this, and help me out ?

 

Cheers

VS

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

vps
Fluorite | Level 6 vps
Fluorite | Level 6

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.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Actually yes, a quick google search results in:

http://www.lexjansen.com/phuse/2005/as/as11.pdf

http://stackoverflow.com/questions/25447567/using-vba-to-get-data-from-sas-and-limit-the-data-set

 

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?

vps
Fluorite | Level 6 vps
Fluorite | Level 6
Thanks for the LINK. I have already gone thru this reference and it didnt help me out.
Answering to your question. I am trying to automate something., which cannot be achieve by SAS to CSV.

Do you have any idea what is diff between
obConnection.Properties("Data Source") = "C:\path\"

and

obConnection.Properties("Data Source") = "sas.abc.abc.com:1234"
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

https://msdn.microsoft.com/en-us/library/office/ff837414.aspx?f=255&MSPPError=-2147217396

 

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.

antontansengco
Calcite | Level 5

I think you need to add the Microsoft ActiveX Data Objects 2.8 Library to the References in VBA Project..

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 5282 views
  • 0 likes
  • 3 in conversation