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

Excel VBA Stored Process with parameters

Occasional Contributor
Posts: 11

Excel VBA Stored Process with parameters


I'm trying to run a macro that 1)connects to a SAS server from a remote computer, 2) Submits a command to run a stored process, 3) submits parameters for the stored process.

So far, I had partial success in 1) and 2), but I can't submit a parameter for the stored process. I tried the code: SASproc.Execute "TESTVBA", "param=1", but it didn't work at all.

I tried to insert a macro "GLOBAL param; *processbody; %let param=;", and also tried to set a prompt "param", but neither worked. Any guess anyone? The VBA code goes as follows:

Dim sasObjectFactory As New SASObjectManager.ObjectFactory
Dim obSAS As SAS.Workspace
Dim LoginDef As SASObjectManager.LoginDef

Dim ServerDef As New SASObjectManager.ServerDef
    ServerDef.Protocol = ProtocolBridge
    ServerDef.MachineDNSName = ""
    ServerDef.Port = 8591

Dim SASObjectKeeper As SASObjectManager.ObjectKeeper

Dim obLibRef As SAS.Libref

Dim sasLanguageEvent As SAS.LanguageService

Set sasObjectFactory = New SASObjectManager.ObjectFactory

Set obSAS = sasObjectFactory.CreateObjectByServer("Server", True, ServerDef, "someone", "********")

Set sasLanguageEvent = obSAS.LanguageService

Set SASObjectKeeper = New SASObjectManager.ObjectKeeper
SASObjectKeeper.AddObject 1, "Server", obSAS
Dim SASproc As SAS.StoredProcessService

Set SASproc = obSAS.LanguageService.StoredProcessService
SASproc.Repository = "file:f:\path"
sasLanguageEvent.Submit ParaSAS
SASproc.Execute "TESTVBA", "param=1"

End Sub

Occasional Contributor
Posts: 11

Re: Excel VBA Stored Process with parameters

Well, it turns out it is very easy.

Just use the sasLanguageEvent.Submit to submit a macro command

for example, you can use to submit a parameter set in a A2 cell in Excel:

     SasLanguageEvent.Submit("%let Parameter = " & Plan1.range("A2") & ";")

then run a Stored Process which you use this command such as:

     data weekday;

     dayofWeek = "&Parameter";


You also can submit a data step which creates a set of parameters such as:

     dim SAScommand(2) as string

     SAScommand(0) = "data createdata;"

     SAScommand(1)="data1=" & Plan1.Range("a1") & "; data2 =" & Plan1.Range("a2") & ";"


With those basically there's nothing you can not do Smiley Happy

You just have to be aware of some kinds of variables, such as dates and decimal numbers.


Posts: 1,290

Re: Excel VBA Stored Process with parameters


Thanks for posting your solution.

I'm just starting to play with Excel<->Stored Process integration via VBA.

Was wondering if you could explain why you went with your approach, rather than using VBA to script the excel-addin, as described in:



Occasional Contributor
Posts: 11

Re: Excel VBA Stored Process with parameters

Well, there's a very simple explanation. This paper uses many new features available from Office-Addin 4.3. Where I work they installed 4.2 and the upgrade seems to be far away...

Anyway, I think it is always good to have some tricks under the sleeve, isn't it?

Posts: 1,290

Re: Excel VBA Stored Process with parameters

Abosolutely, always nice to see more than one way to approach a problem!

Posts: 1

Re: Excel VBA Stored Process with parameters


In your SAS code (i.e. your stored process), after defining your variables, add the following line of code:


All your remaining SAS code should follow.

Hope this helps.

Ask a Question
Discussion stats
  • 5 replies
  • 3 in conversation