topic Re: Example code to call a stored process from VBA? in Developers

Example code to call a stored process from VBA?

ScottBass — Mon, 21 Jul 2014 12:56:44 GMT

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+%22SAS+Integration+Technologies%22 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).

Re: Example code to call a stored process from VBA?

AndreasMenrath — Fri, 25 Jul 2014 08:14:21 GMT

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.

Re: Example code to call a stored process from VBA?

ScottBass — Mon, 01 Sep 2014 00:41:29 GMT

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

Re: Example code to call a stored process from VBA?

ChrisBrooks — Thu, 04 Sep 2014 11:43:53 GMT

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

Re: Example code to call a stored process from VBA?

ScottBass — Wed, 08 Oct 2014 05:14:06 GMT

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

Re: Example code to call a stored process from VBA?

FriedEgg — Wed, 08 Oct 2014 16:36:30 GMT

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.