topic Re: Excel VBA Stored Process with parameters in Microsoft Integration with SAS

Excel VBA Stored Process with parameters

KenjiAneel — Fri, 30 Dec 2011 11:40:47 GMT

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

Re: Excel VBA Stored Process with parameters

KenjiAneel — Thu, 19 Jul 2012 14:36:48 GMT

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

Re: Excel VBA Stored Process with parameters

Quentin — Tue, 24 Jul 2012 23:36:45 GMT

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.

Re: Excel VBA Stored Process with parameters

KenjiAneel — Wed, 25 Jul 2012 14:03:50 GMT

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?

Re: Excel VBA Stored Process with parameters

Quentin — Wed, 25 Jul 2012 14:24:14 GMT

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

Re: Excel VBA Stored Process with parameters

Dan07 — Wed, 06 Nov 2013 03:48:50 GMT

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.