streaming output

[AshleyM]

I’ve created a stored process and would like to allow end users the ability to control for output formats before running a stored process. By default, HTML works with the code below. I’m just not certain how to give users the option to produce output that opens directly in SPSS or Excel or as a PDF.

*ProcessBody;

%stpbegin;

proc print data=work.test;

run;

%stpend;

[Cynthia_sas]

Hi:

  If you are going to allow your users to select a destination, then you will have to double check what they select. For example, if the client application is Excel, then you can't let them select PDF. If they are in EG or the Stored Process Web App then you can let them select PDF. If they are in Web Report Studio, then no matter WHAT they select, the WRS interface will change ANYTHING they select to SASReport XML, same thing for PowerPoint. Assuming that you can educate or trust your users to select the right choice for destination based on the client application they are using, then your idea can work.

  But, it is going to be a little more work for you. The only client apps that will accept or receive PDF output, for example, are EG, the Information Delivery Portal or the Stored Process Web App. If you are going to use any client other than EG, you will have to send an STPSRV_HEADER. If you are using EG or your users will be submitting the stored process from EG, then you can, I think use the simple %STPBEGIN override method.

  In your post you mentioned opening stored process output in SPSS -- I'm not exactly sure how you can execute a stored process from inside SPSS -- so none of my response applies to THAT part of your post.

   Here's one example of a stored process. Assume you have a PROMPT item called WANTDEST and the user choices for WANTDEST are something like this:

Displayed    Macro Variable Value

Value            for WANTDEST

-------------------       -----------------------------------------
HTML            MSOFFICE2K
Excel           CSV
Word            RTF
Adobe Reader    PDF

So, if the user selects Excel, the PROMPT will send WANTDEST to the stored process program as a global macro variable with the value of CSV or if the user selects HTML, the macro variable WANTDEST will contain the value MSOFFICE2K. In the simplest method, you override &_ODSDEST before the invocation of  %STPBEGIN, like this:

%global wantdest;
*ProcessBody;

%let _odsdest=&wantdest;   /* <--- this value will come from prompt interface */

%stpbegin;

proc print data=work.test;
run;

%stpend;

   
However, the pitfall of this approach is that not every client application can "receive" all types of output. For example, using the SAS Add-in for Microsoft Office, and Excel, Excel can "receive" HTML results, SASReport XML results and CSV results. PowerPoint, on the other hand, can only "receive" SASReport XML results. Word can "receive" HTML, SASReport XML and RTF results. Excel cannot, for example "receive" PDF results, just as PowerPoint cannot "receive"  CSV or HTML results.

  If your user is sitting at PowerPoint, using the Add-in for Microsoft office and they select CSV as the destination, then since PowerPoint cannot "receive" CSV, the two possibilities are that 1) the stored process would not work or 2) behind the scenes the result type would be changed to SASReport XML, thus defeating the purpose of allowing the user to select.
   
  If you are using the STORED PROCESS WEB APPLICATION (SPWA) or using a direct URL to invoke your stored process, then you may need to use the STPSRV_HEADER function to create the correct content-type HTTP header for your output -- but that is a more advanced usage. For most client applications, it is sufficient to override _ODSDEST, once you understand the limitations of what the client apps can "receive" from a stored process.

  This is an older paper (from SGF and NESUG) that talks about using stored process prompt values as macro variables and using ODS Options as prompt values.

http://www.nesug.org/proceedings/nesug07/ap/ap22.pdf  Although it was written for an older version of the BI Platform, the section on converting the "regular" SAS program to a stored process is still good. Look especially at page 13 where a %SETOPT macro is used to adjust the statements and options for the program based on the destination that the user selected.

  There have been quite a few postings in this forum on the use of STPSRV_HEADER to return stored process results in the Portal and using a direct URL to a web client. Searching for those posts and looking in the Stored Process Developer's Guide will be useful, depending on how/where you are going to submit your stored process. You did not indicate the client applications that were involved -- you said, for example that you wanted to create output that would open directly "as a PDF" but did not indicate which client application would be used to request the stored process. For example, PowerPoint, Web Report Studio, Excel and Word are NOT able to "receive" PDF output from a stored process. So when you say you want stored process results "as a PDF", my assumption is that you intend to have the stored process submitted from EG, the SPWA, the Information Delivery Portal or a custom web application or service.

  A stored process has to be submitted from some application that knows how to contact the metadata server to get the metadata information for the stored process. Those client applications have limitations on the types of output they can "receive" from a stored process. Allowing your users to select the destination is going to put more burden on you as the stored process developer. On the other hand, if you just did a simple stored process and allowed each user to select the Result Type in their client application (under the SAS--> Options menu or the Preferences menu), then you would not have to worry about overriding &_ODSDEST or controlling it for inappropriate choices.

  
cynthia

[AshleyM]

Thank you Cynthia. Yes, I am using EG.

While your code works, I would prefer the stored process to return output directly in Excel.  I tried using this approach below and it still did not work.

/* This stored process returns an Excel file to the user's Web browser */

data work.test;
  rc = stpsrv_header('Content-type','application/vnd.ms-excel');
  rc = stpsrv_header('Content-disposition','attachment; filename=temp.xls');
run;

%let _ODSDEST=Tagsets.excelxp;

%stpbegin;

   proc print data=work.test;
   run;

%stpend;

[Cynthia_sas]

Hi:

  There have been many previous forum postings on using TAGSETS.EXCELXP in a stored process. Once you use this destination, then you can only submit your stored process from the Stored Process Web App, via direct URL (assuming that you know the URL to invoke a stored process) or in the Information Delivery Portal or in a custom HTML front end that you write (such as an HTML form).This all goes back to the "not every client app can "receive" every kind of destination output" explanation. TAGSETS.EXCELXP creates Microsoft Office 2003 "flavor" of XML and not all the BI client applications can "receive" that kind of output.

  I'd suggest searching for some of those prior posts.

cynthia