Hi,
Does anyone have example code that calls a stored process and loads the results in a VBA recordset?
I envisage (at least) two approaches:
1) call the stored process via IOM.
2) call a web service (against the stored process web application server?) to return an XML stream. I'd prefer anonymous authentication against the SPWA server.
For my needs, the data volumes returned are small: one record, around 10 columns. Either approach would meet my needs.
I've looked at the examples in http://support.sas.com/kb/?ct=51000&qm=3&la=en&qt=offering:%22INTTECH%22+contenttype:%22sample%22+%2... but am failing to connect the dots. If there are examples in the doc then I need a better search string - please point me in the right direction .
Thanks,
Scott
P.S.: A perfect example would be a stored process against sashelp.class, with parameters name, sex, and age. One or more parms would subset the data, returning the matching row(s). The recordset would then be manipulated by the VBA for whatever (as simple as echoing to a MsgBox).
This is complicated.
A STP is not a STP. Every SAS version brings modifications and new features for STPs so a STP works different in each SAS version.
Also there are different ways and technologies to run a STP: Java, .Net and COM. With VBA you have picked the most challenging technology!
If you have licenced the SAS Addin for Microsoft Office you may use the API of the SAS Addin (see http://support.sas.com/resources/papers/proceedings11/012-2011.pdf). If you don't have SAS AMO you should consider using another technology.
IMHO calling the STP as a BI web service will be the easiest way.
Hi,
Although I haven't updated this in a while, it's still an outstanding issue.
I've read the SAS 9.4 Integration Technologies- Overview.pdf, 9.4 BI Web Services- Developer's Guide.pdf, and SAS 9.4 Integration Technologies- Windows Client Developer's Guide.pdf documentation, as well as searched for relevant SGF papers.
So, my question is, does anyone have a good example of creating a REST web service in SAS?
Thanks,
Scott
Here's some code for calling a Stored Process from VBA using SAS IOM
' Ensure all variables are declared before use.
Option Explicit
' Initialise SAS Workspace objects.
Public obSAS As SAS.Workspace
Public obWorkspaceManager As New SASWorkspaceManager.WorkspaceManager
Private Sub runBtn_Click()
' Initialise objects for use in connection.
Dim errorString As String
Dim sourcebuffer As String
Dim obObjectFactory As New SASObjectManager.ObjectFactory
Dim obObjectKeeper As New SASObjectManager.ObjectKeeper
Dim obServerDef As New SASObjectManager.ServerDef
Dim obStoredProcessService As SAS.StoredProcessService
Dim logLines() As String
Dim carriageControls() As SAS.LanguageServiceCarriageControl
Dim lineTypes() As SAS.LanguageServiceLineType
Dim line As Variant
Dim logbuffer As String
Dim numlines As Long
' Define SAS connection details.
obServerDef.Port = 8591
obServerDef.Protocol = SASWorkspaceManager.Protocols.ProtocolBridge
obServerDef.MachineDNSName = "<dnsname>"
' Create a new SAS workspace instance on remote SAS Workspace server.
Set obSAS = obObjectFactory.CreateObjectByServer("myName", True, obServerDef, "<username>", "<password>")
Call obObjectKeeper.AddObject(1, "WorkspaceObject", obSAS)
' Assign a dummy location to the SAS _WEBOUT file [IMPORTANT STEP].
sourcebuffer = "filename _WEBOUT dummy;"
obSAS.LanguageService.Submit sourcebuffer
' Call the 'test_iom' SAS Stored Process.
Set obStoredProcessService = obSAS.LanguageService.StoredProcessService
obStoredProcessService.Repository = "file:" & "StoredProcesses/admin/testing"
obStoredProcessService.Execute "test_iom", ""
' Search the SAS log for an instance of a SAS ERROR message.
numlines = 2000
obSAS.LanguageService.FlushLogLines numlines, carriageControls, lineTypes, logLines
For Each line In logLines
logbuffer = logbuffer & line & vbCrLf
If InStr(1, logbuffer, "ERROR:") Then
GoTo ErrHandler
End If
Next line
' Close connection to SAS server if process runs successfully.
GoodEnd:
If Not (obSAS Is Nothing) Then
obObjectKeeper.RemoveObject obSAS
obSAS.Close
End If
Exit Sub
' Display message box showing SAS ERROR message if found. Close connection to SAS server.
ErrHandler:
MsgBox "Fatal Error in Stored Process Execution " & line
If Not (obSAS Is Nothing) Then
Call obObjectKeeper.RemoveAllObjects
Call obSAS.Close
End If
End Sub
Obviously you will need to replace the values between angled brackets <> with appropriate ones for your installation.
When it comes to getting the output into Excel I'd use ADO - there are quite a few examples on the web, not least two in my paper from this years SAS Global Forum.... 🙂 http://support.sas.com/resources/papers/proceedings14/1598-2014.pdf
Chris
Hi Chris,
Thanks for this, and apologies for the late reply. I'm juggling several projects at the moment.
I'm sure this will be the approach I'll use, but I'm still interested in an example of creating a REST web service using SAS as the back end. If this is possible, can someone send me some pointers?
The data flow is from SAS --> Access, but ADO still applies.
Regards,
Scott
Once you have a stored process deployed to a web application, you essentially have a RESTful web service:
ex.
http://yourserver/SASStoredProcess/do?_program=/WebApps/Sales/Weekly+Report&region=West
If my stored process /WebApps/Sales/Weekly Report output XML or JSON I would be able to consume it like any other web service api.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.