topic Re: Run a SAS program from Visual Basic in Excel in Microsoft Integration with SAS

Run a SAS program from Visual Basic in Excel

deleted_user — Tue, 18 May 2010 13:35:13 GMT

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.

Re: Run a SAS program from Visual Basic in Excel

sbb — Tue, 18 May 2010 13:54:43 GMT

Recommended Google advanced search argument, this topic/post:

+"visual basic" site:sas.com

Scott Barry
SBBWorks, Inc.

Re: Run a SAS program from Visual Basic in Excel

CaseySmith — Tue, 15 Aug 2017 13:35:59 GMT

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

Re: Run a SAS program from Visual Basic in Excel

data_null__ — Thu, 20 May 2010 10:51:08 GMT

> 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?

Re: Run a SAS program from Visual Basic in Excel

CaseySmith — Fri, 21 May 2010 18:38:49 GMT

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

Re: Run a SAS program from Visual Basic in Excel

sasfrum2011 — Mon, 21 Mar 2011 19:16:16 GMT

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.

Re: Run a SAS program from Visual Basic in Excel

huyns — Wed, 14 May 2014 10:03:54 GMT

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

Re: Run a SAS program from Visual Basic in Excel

Amir — Wed, 14 May 2014 12:07:59 GMT

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.

Re: Run a SAS program from Visual Basic in Excel

AdamRyczkowski — Tue, 26 May 2015 10:12:20 GMT

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).