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
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
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.
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?
Abosolutely, always nice to see more than one way to approach a problem!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!