Excel VBA Stored Process with parameters

[KenjiAneel]

Hi,

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 = "just.asample.net"
    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

[KenjiAneel]

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";

     run;

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") & ";"

     SAScommand(2)="run;"

With those basically there's nothing you can not do

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

Rgds

[Quentin]

Hi,

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:

http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

Thanks,

--Q.

[KenjiAneel]

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?

[Quentin]

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

[Dan07]

Solution:

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

*processbody;

All your remaining SAS code should follow.

Hope this helps.