Writing a stored process for Generated Web Service (SAS BI Web Services)

[deleted_user]

Hi,
In the documentation for SAS BI WebServices for XMLA (http://support.sas.com/documentation/cdl/en/wbsvcdg/61496/HTML/default/sample.htm) there is a good explanation of how to write a stored process for an XMLA web service. But we are looking to expose the sproc as a Generated Web Service (the new way in 9.2). I am looking to understand how to write the sproc if its going to be exposed as a "Generated web service".

I also searched in the "samples" database but had no luck. Did anyone try to expose a stored process as a Generated web service? If so, can you share some sample code? Essentially, I would like to understand what magic needs to be done in the sproc to read the input parameters and send the output parameters.

Thanks,
Sudhir

[Vince_SAS]

This link is right below the one you referenced:

Using Generated Web Services
http://support.sas.com/documentation/cdl/en/wbsvcdg/61496/HTML/default/a003257649.htm

Does that provide the information that you need?

Vince DelGobbo
SAS R&D

[deleted_user]

No.

The link you provided gives me an idea of how to expose a sproc as a "Generated web service". But I am looking to find out how to the sproc needs to be written to read the input parameters.

For example, in the documentation for XMLA web services the suggestion is to write the sproc something like this -

----------------------------------
%put &tablename

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

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

is this how we would write the sproc to be callable from a Generated web service?

[Cynthia_sas]

Hi:
SAS Stored Process input parameters are surfaced to your SAS session as GLOBAL SAS Macro variables. So, your stored process needs to use SAS Macro variables in an appropriate manner.

For example, &TABLENAME in the code you show is a Macro variable, whose value will be supplied either from a %LET statement in the Stored Process code; from a CALL SYMPUT in the Stored Process code or from an INPUT parameter value for which the user is prompted in one of the prompting interfaces in either a client application or from a default which is set in the stored process parameter registration process.

To understand how to write a stored process, you can review the developer's documentation:
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/index.html
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/program.html
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/stpmacro.html
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/stpsamp.html

And, to understand how to use SAS Macro variables in a SAS Stored Process, these papers might prove useful:
http://www.nesug.org/Proceedings/nesug07/ap/ap22.pdf
http://www2.sas.com/proceedings/forum2007/023-2007.pdf
http://www2.sas.com/proceedings/forum2008/024-2008.pdf

The difference between surfacing a stored process in a client application and a web service does require some adjustment in your code. For example, it is explained here:
http://support.sas.com/rnd/itech/doc9/dev_guide/websrvcs/program.html

Quoted from above page:
" The following list explains unique details about Web service stored processes:
-- The data returned by the stored process must be XML.

--The %STPBEGIN or %STPEND macros are not used with Web service stored processes. These macros set up ODS statements for the stored process, but Web services do not use ODS.

--Web service stored processes produce streaming results, which means that the SAS program writes output to _WEBOUT, typically by using the following LIBNAME statement: libname _WEBOUT xml xmlmeta=&_XMLSCHEMA;

-- The _XMLSCHEMA macro is unique to Web services. This macro is passed to the SAS program when it is invoked from the Web service. The _XMLSCHEMA macro is set to one of three values depending on the Content property that gets passed to the Execute call.

-- Input streams are unique to Web services. Input streams are filerefs that contain XML. "

So in a sense, in order to understand how to write a stored process for a generated web service you need to:
1) understand some of SAS Macro facility basics (the paper is a good resource: http://www2.sas.com/proceedings/sugi28/056-28.pdf )
2) understand the basics of how macro variables will contain the stored process input parameter values
3) understand how to write a "regular" stored process (perhaps test your stored process in a client application like EG)
4) then apply the changes quoted above to your code for the web services stored process usage. (Such as adding &_XMLSCHEMA and using _webout appropriately and making sure that the results are streamed back to the web service.)

cynthia