Sample PROC MEANS Using SAS BI Web Services

Sample Overview

This sample shows how to write, define, and invoke a sample stored process that can be used with SAS BI Web Services. This example is for an XMLA web service. You can access other sample web services in the samples database at support.sas.com.

Write the Stored Process

The following SAS code is a sample stored process called stpwsmea.sas. This program is installed with SAS Integration Technologies. On Windows, the default location is <SASHOME>\SASFoundation\9.4\inttech\sample. On UNIX, the default location is <SASHOME>/SASFoundation/9.4/samples/inttech.
%put &tablename

libname _WEBOUT xml xmlmeta = &_XMLSCHEMA;
libname instream xml;

proc means data=instream.&tablename
   output out=_WEBOUT.mean;
run;

libname _WEBOUT clear;
libname instream clear;

Define the Metadata

The stored process must be defined on a SAS Metadata Server that is used by SAS BI Web Services in order to determine how and where to run the stored process. Stored process metadata is defined by using SAS Management Console. The tables in this section show the values for each field in the New Stored Process wizard in SAS Management Console.
Note: If you have previously installed the SAS Stored Process sample metadata as part of the SAS Deployment Wizard or the Web Infrastructure Platform installation, then you might not need to re-create the metadata for the "Sample: MEANS Procedure Web Service" sample stored process. The sample metadata should already be available from the /Products/SAS Intelligence Platform/Samples folder. If you do not have the sample metadata, you can define the metadata for the stored process on your SAS Metadata Server by performing the following steps.
  1. Open SAS Management Console and connect to the appropriate metadata server.
  2. From the SAS Management Console navigation tree, select the folder under which you would like to create the new stored process. (If you would like to create a new folder, you can select the location in the navigation tree in which you want to add the new folder, and then select Actionsthen selectNewthen selectFolder from the menu bar to open the New Folder wizard. Follow the wizard instructions to create the new folder.)
  3. After you select the folder in which you want to add a new stored process, select Actionsthen selectNewthen selectStored Process from the menu bar. The New Stored Process wizard displays.
  4. On the first page of the New Stored Process wizard, enter the following values in their corresponding fields for the sample web service:
    Field Values for the New Stored Process Wizard
    Field
    Value
    Name
    Sample: MEANS Procedure Web Service
    Keywords
    XMLA Web Service
    Note: To add the keyword, click Add to open the Add Keyword dialog box, and then enter the name of the keyword. Click OK. Adding a description and roles for the stored process are optional.
  5. Click Next.
  6. Enter the following values in their corresponding fields for the sample web service:
    Values for the Sample Web Service
    Field
    Value
    Application server
    SASApp
    Server type
    Stored process server only
    Source code location and execution
    Allow execution on selected application server only
    Store source code on application server
    Source code repository
    <SASHOME>\SASFoundation\9.4\samples\inttech
    Source code file
    stpwsmea.sas
    Results
    Stream
    Click Next.
  7. Click New Prompt to add an input parameter to the stored process.
  8. On the General tab, enter the following values in their corresponding fields for the sample web service:
    Values for the Prompt
    Field
    Value
    Name
    tablename
    Displayed text
    tablename
  9. Select the Requires a non-blank value check box. Entering a description is optional.
  10. On the Prompt Type and Values tab, enter the following values in their corresponding fields for the sample web service:
    Values for the Prompt
    Field
    Value
    Prompt type
    Text
    Method for populating prompt
    User-entered value
    Number of values
    Single value
    Text type
    Single line
    Default value
    InData
  11. Click Next.
  12. Click New to open the New Data Source dialog box, where you must define the data source.
    1. Enter the following values in their corresponding fields for the sample web service:
      Values for the New Data Source
      Field
      Value
      Type
      XML Data Source
      Label
      instream
      Fileref
      instream
      Expected content type
      text/xml
    2. You must also select the Allow rewinding stream check box in the New Data Source dialog box. Otherwise, an XMLMap would need to be specified on the XML LIBNAME statement to define the XML schema for instream.
    3. Click OK to save the data source definition.
  13. Review your stored process information, and click Finish to define the metadata for the stored process.

Invoke the Stored Process

SOAP Request

The stored process that we just created can be invoked by SAS BI Web Services for Java middle-tier clients. A web service client invokes the middle-tier web service with an Execute() command. The SOAP request body, or client code, follows:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:sas="urn:schemas-microsoft-com:xml-analysis">
   <soapenv:Header/>
   <soapenv:Body>
      <sas:Execute>
         <sas:Command>
            <StoredProcess name="/Products/SAS Intelligence Platform/Samples/
               Sample: MEANS Procedure Web Service">
               <Parameter name="tablename">InData</Parameter>
               <Stream name="instream">
                  <Table>
                     <InData>
                        <Column1>1</Column1>
                        <Column2>20</Column2>
                        <Column3>99</Column3>
                     </InData>
                     <InData>
                        <Column1>50</Column1>
                        <Column2>200</Column2>
                        <Column3>9999</Column3>
                     </InData>
                     <InData>
                        <Column1>100</Column1>
                        <Column2>2000</Column2>
                        <Column3>1000000</Column3>
                     </InData>
                  </Table>
               </Stream>
            </StoredProcess>
         </sas:Command>
         <sas:Properties>
            <PropertyList>
               <DataSourceInfo>Provider=SASSPS;</DataSourceInfo>
            </PropertyList>
         </sas:Properties>
      </sas:Execute>
   </soapenv:Body>
</soapenv:Envelope>

SOAP Response

After you run the client code, the resulting SOAP response body is as follows:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <n:ExecuteResponse xmlns:n="urn:schemas-microsoft-com:xml-analysis">
         <n:return>
            <TABLE>
               <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
                  <xs:element name="TABLE">
                     <xs:complexType>
                        <xs:sequence>
                           <xs:element ref="MEAN" minOccurs="0" maxOccurs="unbounded"/>
                        </xs:sequence>
                     </xs:complexType>
                  </xs:element>
                  <xs:element name="MEAN">
                     <xs:complexType>
                        <xs:sequence>
                           <xs:element name="_TYPE_" minOccurs="0" type="xs:double"/>
                           <xs:element name="_FREQ_" minOccurs="0" type="xs:double"/>
                           <xs:element name="_STAT_" minOccurs="0">
                              <xs:simpleType>
                                 <xs:restriction base="xs:string">
                                    <xs:maxLength value="8"/>
                                 </xs:restriction>
                              </xs:simpleType>
                           </xs:element>
                           <xs:element name="COLUMN3" minOccurs="0" type="xs:double"/>
                           <xs:element name="COLUMN2" minOccurs="0" type="xs:double"/>
                           <xs:element name="COLUMN1" minOccurs="0" type="xs:double"/>
                        </xs:sequence>
                     </xs:complexType>
                  </xs:element>
               </xs:schema>
               <MEAN>
                  <_TYPE_>0</_TYPE_>
                  <_FREQ_>3</_FREQ_>
                  <_STAT_>N</_STAT_>
                  <COLUMN3>3</COLUMN3>
                  <COLUMN2>3</COLUMN2>
                  <COLUMN1>3</COLUMN1>
               </MEAN>
               <MEAN>
                  <_TYPE_>0</_TYPE_>
                  <_FREQ_>3</_FREQ_>
                  <_STAT_>MIN</_STAT_>
                  <COLUMN3>99</COLUMN3>
                  <COLUMN2>20</COLUMN2>
                  <COLUMN1>1</COLUMN1>
               </MEAN>
               <MEAN>
                  <_TYPE_>0</_TYPE_>
                  <_FREQ_>3</_FREQ_>
                  <_STAT_>MAX</_STAT_>
                  <COLUMN3>1000000</COLUMN3>
                  <COLUMN2>2000</COLUMN2>
                  <COLUMN1>100</COLUMN1>
               </MEAN>
               <MEAN>
                  <_TYPE_>0</_TYPE_>
                  <_FREQ_>3</_FREQ_>
                  <_STAT_>MEAN</_STAT_>
                  <COLUMN3>336699.333</COLUMN3>
                  <COLUMN2>740</COLUMN2>
                  <COLUMN1>50.3333333</COLUMN1>
               </MEAN>
               <MEAN>
                  <_TYPE_>0</_TYPE_>
                  <_FREQ_>3</_FREQ_>
                  <_STAT_>STD</_STAT_>
                  <COLUMN3>574456.555</COLUMN3>
                  <COLUMN2>1094.89726</COLUMN2>
                  <COLUMN1>49.5008417</COLUMN1>
               </MEAN>
            </TABLE>
         </n:return>
      </n:ExecuteResponse>
   </soapenv:Body>
</soapenv:Envelope>