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.


Trusted Advisor
Posts: 1,138

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?

Trusted Advisor
Posts: 1,138

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.

Post a Question
Discussion Stats
  • 5 replies
  • 3 in conversation