Executing query with joins in VBA

Reply
Occasional Contributor vps
Occasional Contributor
Posts: 14

Executing query with joins in VBA

Hi,

I am trying to exeucte export data via VBA excution. I am able to do this when I am doing for a single table.How can I do the execution for query where I am doing multiple table joins? 

 

Can anyone let me know how to execute join query of sas in VBA macro or can you also suggest if I want to execute bulk SAS proc and get the result in excel. 

 

I want to achieve this via VBA macro run only. So please guide me on that direction.  Thanks

 

Cheers

Super User
Super User
Posts: 7,668

Re: Executing query with joins in VBA

Perhaps if you showed how you get a single table out of SAS that would help, as this isn't a VBA forum, and personally I have not heard of anyone doing things in this manner.  Simple solution, do all your processing in SAS, then export the results either direct to Excel, or to CSV for further processing by Excel macro.  Don't try to treat Excel as a database which it really isn't.

Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Executing query with joins in VBA

I am not treating EXCEL as DB. Hope you got my question what I am trying ?
Super User
Super User
Posts: 7,668

Re: Executing query with joins in VBA

You haven't answered the question or provided the code.  Why not export the data from SAS directly to Excel/XML/CSV?  What do you hope to gain from using a database connection from a non-database, to a non-database, that you don't get just by using the available tools?  If you want to do databasing, then use database software and then from SAS directly load the data into the database using proc sql.

Super User
Posts: 5,375

Re: Executing query with joins in VBA

The "proper" ways to access data is by ODBC, OLEDB or of you are using a MS Office application Add in for MS Office.
Data never sleeps
Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Executing query with joins in VBA

Linush - I am using ADODB to get this done. All I am looking is
1. How to get the result set with multiple table (similar how we do joins)
2. Instead of * I need only selected columns to be shown. How can I achieve this in VBA using SAS.

Cheers
Super User
Posts: 5,375

Re: Executing query with joins in VBA

You str still not telling us how you are doing the actual data access. I have no experience with adodb but on sourceforge it's described as a library for php. And the only listed driver there that you could use for SAS is ODBC.
Data never sleeps
Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Executing query with joins in VBA

Sorry - couldnt get time to post the code . here it is

Public Sub GetData()


Dim sGetfilter As String
Dim sGetQuery As String

Set obConnection = New ADODB.Connection
obConnection.Provider = "sas.IOMProvider"
obConnection.Properties("Data Source") = "_LOCAL_"
obConnection.Open

Set obRecordset = New ADODB.Recordset

obRecordset.Open "da.Today_dim", obConnection, adOpenDynamic, adLockReadOnly, ADODB.adCmdTableDirect '

'add header
Cells(1, 1).Select$
For i = 0 To obRecordset.Fields.Count - 1
ActiveCell.Offset(0, i).Value = obRecordset.Fields(i).Name
Next i

Cells(2, 1).Select
ActiveCell.CopyFromRecordset obRecordset

obRecordset.Close
Set obRecordset = Nothing
obConnection.Close
Set obConnection = Nothing

End Sub


If notice, my connection will get data from da.today_dim table for all the column and rows. Now If I want to join the table with another table and get limited set of record. How should i be doing that ?

cheers
Super User
Super User
Posts: 7,668

Re: Executing query with joins in VBA

"You haven't answered the question or provided the code.  Why not export the data from SAS directly to Excel/XML/CSV?  What do you hope to gain from using a database connection from a non-database, to a non-database, that you don't get just by using the available tools?  If you want to do databasing, then use database software and then from SAS directly load the data into the database using proc sql."

 

See above.  SAS is not a database, it is a series of unconnected libraries and datasets.  If you want to do databasing use a database.  If you just want data out into Excel, process the data in SAS, then export it.

Occasional Contributor vps
Occasional Contributor
Posts: 14

Re: Executing query with joins in VBA

Code is provide up before. Guess you didnt notice. I know sas is not database. But certain thing I want to try out this way and it is possible. Even earlier many comment I got we cant do this. But I achieved it as mentioned above.
I know we can target a proc query to get result. So I posted this out.
I hardly get answer for what I ask. Only I get is suggestion.

Thanks
Ask a Question
Discussion stats
  • 9 replies
  • 506 views
  • 0 likes
  • 3 in conversation