Example code to call a stored process from VBA?

[ScottBass]

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).

[AndreasMenrath]

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.

[ScottBass]

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

[ChrisBrooks]

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

[ScottBass]

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

[FriedEgg]

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&amp;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.