12-30-2011 06:40 AM
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"
SASproc.Execute "TESTVBA", "param=1"
07-19-2012 10:36 AM
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:
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
You just have to be aware of some kinds of variables, such as dates and decimal numbers.
07-24-2012 07:36 PM
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:
07-25-2012 10:03 AM
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?
11-05-2013 10:48 PM
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.