We’re smarter together. Learn from this collection of community knowledge and add your expertise.

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

by SAS Employee StephenFoerster on ‎01-19-2016 10:13 AM - edited on ‎01-20-2016 09:44 AM by SAS Employee AllenCunningham (883 Views)

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.

 

  1. First, let's build a simple web service in SAS Data Management 2.6 that predicts gender. Pic1.png
  2. 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"
  3. 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>
    
  4. 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
  5. Finally, I submit the program and examine the delivered result via a message box. Pic2.png


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.

Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.