BookmarkSubscribeRSS Feed
Rhodochrosite | Level 12


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



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

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Pyrite | Level 9

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

Rhodochrosite | Level 12


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?



Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Ammonite | Level 13

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.


    If Not (obSAS Is Nothing) Then


        obObjectKeeper.RemoveObject obSAS



    End If


    Exit Sub


' Display message box showing SAS ERROR message if found. Close connection to SAS server.


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


Rhodochrosite | Level 12

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.



Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
SAS Employee

Once you have a stored process deployed to a web application, you essentially have a RESTful web service:



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.



Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.


Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 


Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4 in conversation