BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
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.
1 ACCEPTED SOLUTION

Accepted Solutions
CaseySmith
SAS Employee
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

Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

View solution in original post

8 REPLIES 8
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Recommended Google advanced search argument, this topic/post:

+"visual basic" site:sas.com


Scott Barry
SBBWorks, Inc.
CaseySmith
SAS Employee

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


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

data_null__
Jade | Level 19
> 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?
CaseySmith
SAS Employee
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

Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

huyns
Calcite | Level 5

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

Amir
PROC Star

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.

AdamRyczkowski
Calcite | Level 5

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

sasfrum2011
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 8 replies
  • 25834 views
  • 3 likes
  • 8 in conversation