How to call a SAS Data Management 2.6 Web Service from Excel (VBA)

3 Likes

The web service capabilities expand with each release of SAS Data Management Server, and version 2.6 is no exception with its job-specific WSDL (Web Services Description Language) offerings.

SAS Data Management Web Services are extremely useful for integrating SAS Data Management's data quality functionality into operational systems. Imagine a data entry professional entering a new prospect into a system with the name, "David Smith." This could trigger a SAS Data Management web service that performs a fuzzy lookup to the system and returns the message, "Did you mean 'David Smyth?' He's already in the system." Let's look at calling a SAS Data Management 2.6 Web Service from Microsoft Excel to simulate a similar scenario.

First, let's build a simple web service in SAS Data Management 2.6 that predicts gender.
Next, we load the service to the SAS Data Management Server. Once in the server, we can get our WSDL; in this case, "http://10.38.11.37:21036/datasvc/Determine_Gender.ddf?wsdl"

Using the wsdl URL, we can determine the required SOAP request format using SOAPUI or an equivalent tool. Using SoapUI, I can see that the required SOAP request looks like this:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:arc="archserver.xsd.dataflux.com">
   <soapenv:Header></soapenv:Header>
   <soapenv:Body>
      <arc:datasvc_Determine__Gender.ddf_in>
         <!--Optional:-->
         <table_>
            <!--1 or more repetitions:-->
            <row>
               <Name>Joe Smith</Name>
            </row>
         </table_>
      </arc:datasvc_Determine__Gender.ddf_in>
   </soapenv:Body>
</soapenv:Envelope>

With the request format, I can now build my Office VBA program that looks like this. The program simply calls the web service using the XMLHTTP object (Reg.Open) and sends the request which was loaded into a string named "j" (Req.Send).

Private Sub CommandButton1_Click()
  Dim WS As Worksheet: Set WS = ActiveSheet
  Dim Req As New XMLHTTP
  Dim j As String
  j = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' 
     xmlns:arc='archserver.xsd.dataflux.com'>" & vbCrLf & _
     "<soapenv:Header></soapenv:Header>" & vbCrLf & _
   "<soapenv:Body>" & vbCrLf & _
      "<arc:datasvc_Determine__Gender.ddf_in>" & vbCrLf & _
         "<!--Optional:-->" & vbCrLf & _
         "<table_>" & vbCrLf & _
            "<!--1 or more repetitions:-->" & vbCrLf & _
            "<row>" & vbCrLf & _
               "<Name>Joe Smith</Name>" & vbCrLf & _
            "</row>" & vbCrLf & _
         "</table_>" & vbCrLf & _
      "</arc:datasvc_Determine__Gender.ddf_in>" & vbCrLf & _
   "</soapenv:Body>" & vbCrLf & _
"</soapenv:Envelope>"

  Req.Open "POST", "http://10.38.11.37:21036/datasvc/Determine_Gender.ddf?wsdl", 
            False, "Student", "Metadata0"
  Req.send j
  
  MsgBox Req.responseText
End Sub

Finally, I submit the program and examine the delivered result via a message box.

Want more?

Building out an entire Excel application is beyond the scope of this post. I just wanted to show how to call the SAS Data Management Service with VBA. However, if you'd like to see such an application built from a web service, check out this YouTube video:

To learn more about building web services in SAS Data Management Studio, check out the SAS Data Management Basics and Advanced courses .

Building SAS Data Management Service applications in C# and Java script would be similar. In Java Script, you would use the XMLHttpRequest object. To see an example of calling a web service with Java Script, check out this video:

One more thing

It should be noted that you can not use the Excel WEBSERVICE function to call a SAS Data Management 2.6 Web Service. The function is only capable of submitting HTTP GET requests and as you can see from the VBA code above, SAS Data Management services require that request text be sent via a POST method. So, VBA is required.