SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Run a SAS program from Visual Basic in Excel

Accepted Solution Solved
Reply
N/A
Posts: 0
Accepted Solution

Run a SAS program from Visual Basic in Excel

Hi !

I'd like to just open the SAS software and then execute a program from Excel.

Is there a syntax in Visual Basic for that?

R.M.

Accepted Solutions
Solution
‎08-15-2017 09:15 AM
SAS Super FREQ
Posts: 307

Re: Run a SAS program from Visual Basic in Excel

I debated posting it, but was afraid it would clutter the thread. But since you ask, here goes...
I put together the sample VBA code below and ran it in Excel. You have to have SAS Integration Technologies client (standalone, or bundled with AMO and EG) installed on the machine to be able to add the required references to the SAS type libraries in the VBA editor in Excel. There are some help files (.chm) in the Integration Technologies installation folder that should provide some guidance on those interfaces.

Good luck!
Casey

Note: The sample uses the 9.2 interfaces, but I'd expect similar code to work in 9.1.3. The sample also connects to a server defined in metadata, but you should be able to change the code to connect to your local server if preferred.

============================

'Add reference to "SAS: Integrated Object Model (IOM)..." and "SASObjectManager ..." (optional)

Sub SubmitCodeTest()
Dim ws As workspace
Dim code As String
Dim log As String

Set ws = ConnectToServerUsingObjectFactory()
code = "data tmp; set sashelp.class; run;"
log = SubmitCode(ws, code)
MsgBox log
End Sub

Function ConnectToServerUsingObjectFactory() As workspace

On Error GoTo ErrHandler

'Requires reference to "SASObjectManager 1.1 Type Library"
Dim obObjectFactory As ObjectFactoryMulti2
'Requires reference to "SAS: Integrated Object Model (IOM) (SAS System 9.2) Type Library"
Dim obSAS As workspace
Dim obObjectKeeper As New SASObjectManager.objectKeeper

Set obObjectFactory = CreateObject("SASObjectManager.ObjectFactoryMulti2")

obObjectFactory.LogEnabled = True

' Use the SAS Object Manager to establish a SAS workspace object.
Set obSAS = obObjectFactory.CreateObjectByLogicalName("SASApp - Logical Workspace Server", "")

MsgBox obObjectFactory.GetCreationLog(False, True)
MsgBox obSAS.UniqueIdentifier

'Add the workspace to the object keeper
obObjectKeeper.AddObject 1, "", obSAS

Set ConnectToServerUsingObjectFactory = obSAS

Exit Function

ErrHandler:
MsgBox Err.Description

End Function

Function SubmitCode(workspace As workspace, code As String) As String

Dim ls As SAS.LanguageService
Dim logBuffer As String
Set ls = workspace.LanguageService

'if we submit asynchronously, we'd have to add a sink and wait to retrieve the log until signalled
ls.Async = False
ls.Submit code

'get the log
logBuffer = ls.FlushLog(100000)

'Could use FlushLogLines to get log instead
'Dim carriageControls() As LanguageServiceCarriageControl
'Dim lineTypes() As LanguageServiceLineType
'Dim logLines() As String
'ls.FlushLogLines 8192, carriageControls, lineTypes, logLines
'For Each line In logLines
' logBuffer = logBuffer & line & vbCrLf
'Next line

SubmitCode = logBuffer
End Function

Function AssignLibref(workspace As workspace, libraryName As String, path As String)
'Assign a libref using the DataService AssignLibref method...
workspace.DataService.AssignLibref libraryName, "", path, ""
End Function

View solution in original post


All Replies
Super Contributor
Super Contributor
Posts: 3,174

Re: Run a SAS program from Visual Basic in Excel

Recommended Google advanced search argument, this topic/post:

+"visual basic" site:sas.com


Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 307

Re: Run a SAS program from Visual Basic in Excel

[ Edited ]

If you are using SAS Add-in for Microsoft Office (AMO), there are a couple custom tasks (written by Chris Hemedinger) that will allow you to simply submit code from AMO. Here is one:

http://support.sas.com/documentation/onlinedoc/guide/customtasks/samples/SASProgramRunnerExample.zip

 

UPDATE: Starting in AMO 7.13 release, you can create and run SAS programs in SAS Add-in for Microsoft Office.


If you don't have AMO, it's a little more involved, but you can submit code via VBA using the LanguageService. I can send sample code (as-is) if desired.

Casey

Respected Advisor
Posts: 3,790

Re: Run a SAS program from Visual Basic in Excel

> LanguageService. I can send sample code (as-is) if
> desired.

Why don't you post the code here for the rest of the forum participants?
Solution
‎08-15-2017 09:15 AM
SAS Super FREQ
Posts: 307

Re: Run a SAS program from Visual Basic in Excel

I debated posting it, but was afraid it would clutter the thread. But since you ask, here goes...
I put together the sample VBA code below and ran it in Excel. You have to have SAS Integration Technologies client (standalone, or bundled with AMO and EG) installed on the machine to be able to add the required references to the SAS type libraries in the VBA editor in Excel. There are some help files (.chm) in the Integration Technologies installation folder that should provide some guidance on those interfaces.

Good luck!
Casey

Note: The sample uses the 9.2 interfaces, but I'd expect similar code to work in 9.1.3. The sample also connects to a server defined in metadata, but you should be able to change the code to connect to your local server if preferred.

============================

'Add reference to "SAS: Integrated Object Model (IOM)..." and "SASObjectManager ..." (optional)

Sub SubmitCodeTest()
Dim ws As workspace
Dim code As String
Dim log As String

Set ws = ConnectToServerUsingObjectFactory()
code = "data tmp; set sashelp.class; run;"
log = SubmitCode(ws, code)
MsgBox log
End Sub

Function ConnectToServerUsingObjectFactory() As workspace

On Error GoTo ErrHandler

'Requires reference to "SASObjectManager 1.1 Type Library"
Dim obObjectFactory As ObjectFactoryMulti2
'Requires reference to "SAS: Integrated Object Model (IOM) (SAS System 9.2) Type Library"
Dim obSAS As workspace
Dim obObjectKeeper As New SASObjectManager.objectKeeper

Set obObjectFactory = CreateObject("SASObjectManager.ObjectFactoryMulti2")

obObjectFactory.LogEnabled = True

' Use the SAS Object Manager to establish a SAS workspace object.
Set obSAS = obObjectFactory.CreateObjectByLogicalName("SASApp - Logical Workspace Server", "")

MsgBox obObjectFactory.GetCreationLog(False, True)
MsgBox obSAS.UniqueIdentifier

'Add the workspace to the object keeper
obObjectKeeper.AddObject 1, "", obSAS

Set ConnectToServerUsingObjectFactory = obSAS

Exit Function

ErrHandler:
MsgBox Err.Description

End Function

Function SubmitCode(workspace As workspace, code As String) As String

Dim ls As SAS.LanguageService
Dim logBuffer As String
Set ls = workspace.LanguageService

'if we submit asynchronously, we'd have to add a sink and wait to retrieve the log until signalled
ls.Async = False
ls.Submit code

'get the log
logBuffer = ls.FlushLog(100000)

'Could use FlushLogLines to get log instead
'Dim carriageControls() As LanguageServiceCarriageControl
'Dim lineTypes() As LanguageServiceLineType
'Dim logLines() As String
'ls.FlushLogLines 8192, carriageControls, lineTypes, logLines
'For Each line In logLines
' logBuffer = logBuffer & line & vbCrLf
'Next line

SubmitCode = logBuffer
End Function

Function AssignLibref(workspace As workspace, libraryName As String, path As String)
'Assign a libref using the DataService AssignLibref method...
workspace.DataService.AssignLibref libraryName, "", path, ""
End Function
N/A
Posts: 1

Re: Run a SAS program from Visual Basic in Excel

I get this error: "Argument  not optional". Do you known why?

Super Contributor
Posts: 282

Re: Run a SAS program from Visual Basic in Excel

Hi,

Welcome to the forum. A quick internet search on your error message provided the following link:

Argument not optional (Visual Basic)

Sounds like an argument is missing, possibly.

It is good practice to start a new question when you need something answering, as mentioned in

If you need more information then you'll need to supply more information, e.g. the relevant part of your program, which would be good to do in a new question, as already mentioned.

If the error is occurring in VB then it might be worth trying to post your question to a VB forum, if you haven't already or if you can't get an answer here.

Regards,

Amir.

N/A
Posts: 1

Re: Run a SAS program from Visual Basic in Excel

Thank you very much. The code works and opens a whole new domain of possibilities.

I have one problem, though:

How to get a macro variable's value into the VBA? My code needs to decide what task to run next based on the SAS' result, and the task might be performed outside the SAS ecosystem (in Oracle or R for instance).

N/A
Posts: 1

Re: Run a SAS program from Visual Basic in Excel

The page
http://support.sas.com/rnd/datavisualization/BridgeForESRI/V1/stored_process_code.htm
was helpful. But how do we modify the syntax to

1. pass dataset in excel worksheet to the macro variable ds local to myprgrm?

Would I need a dde type of statement in myprgrm to convert the Excel worksht that I pass to SAS via VBA into SAS dataset?

2. pass dataset in a folder (non-excel) to the macro variable ds local to myprgrm?

Thanks very much in advance.
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 17396 views
  • 2 likes
  • 8 in conversation