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

Excel VBA Stored Process with parameters

Reply
Occasional Contributor
Posts: 11

Excel VBA Stored Process with parameters

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

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

     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 Smiley Happy

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

Rgds

PROC Star
Posts: 1,290

Re: Excel VBA Stored Process with parameters

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.

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?

PROC Star
Posts: 1,290

Re: Excel VBA Stored Process with parameters

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

N/A
Posts: 1

Re: Excel VBA Stored Process with parameters

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.

Ask a Question
Discussion stats
  • 5 replies
  • 3151 views
  • 0 likes
  • 3 in conversation