The SAS Output Delivery System and reporting techniques

ODS EXCEL in Web StoreProcess

Accepted Solution Solved
Reply
SAS Employee
Posts: 2
Accepted Solution

ODS EXCEL in Web StoreProcess

[ Edited ]

I've tried to use ODS and EXCEL in the Stored Process Web Application (SAS 9.4 M2).

Unfortunately this code:

 

%global  _ODSDEST _ODSOPTIONS _ODSSTYLE;
*ProcessBody;
data _null_;
    rc = appsrv_header('Content-type','application/vnd.ms-excel');
    rc = stpsrv_header('Content-disposition','attachment; filename=test.xlsx');
run;

%let _ODSDEST=EXCEL;
%let _ODSSTYLE=Plateau;

%macro EXPORT;
    %stpbegin;
        proc print noobs label data=sashelp.TOURISM; run;
    %stpend;
%mend;

%EXPORT;

produce error:

 

- 66          STYLE=Plateau nogtitle nogfootnote body=_WEBOUT path=APSWORK.TCAT0000
-                                                ____                                ___
-                                                22                                  22
-                                                202                                 76
- 66       ! (url="/SASStoredProcess/do?_sessionid=00000000-0000-0000-0000-000000000000&_program=replay&_entry=APSWORK.TCAT0000.") CHARSET=' '
- ERROR 22-322: Syntax error, expecting one of the following: ;, AUTHOR, BOX_SIZING, CATEGORY, CLOSE, COMMENTS, CSSSTYLE, DOM, DPI, FILE, GFOOTNOTE, GTITLE, IMAGE_DPI, KEYWORDS, NOGFOOTNOTE, 
-               NOGTITLE, OPTIONS, SASDATE, STATUS, STYLE, TITLE, WORK.  
- 
- ERROR 202-322: The option or parameter is not recognized and will be ignored.
- ERROR 76-322: Syntax error, statement will be ignored.
- 
- WARNING: No output destinations active.

Problem is that there's no BODY in statement ODS EXCEL.

 

Is it possible use ODS EXCEL in Web Store Process? Any suggestions?


Accepted Solutions
Solution
‎05-18-2016 05:30 AM
SAS Employee
Posts: 2

Re: ODS EXCEL in Web StoreProcess

Thanks for Your suggestions. I've found solution. Here's working code:

*ProcessBody;

data _null_;
	rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	rc = stpsrv_header('Content-disposition','attachment; filename=test.xlsx');
run;

ods excel file = _webout ;
ods excel style = Plateau;

proc print noobs label data=sashelp.TOURISM; run;

ods excel close;

This store process is invoking by Stored Process Web App.

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: ODS EXCEL in Web StoreProcess

[ Edited ]

Capture_stp.PNGI have never tried to code STP(Stored Process) from scratch, so I can't really comment on your code part.  From my base programmer background, it is easier to write the code part in EG program editor first, then convert it into STP. The following is my test code on ODS Excel, after converting to STP, it works (honestly I never thought it woundn't)

FILENAME OT "/share/test/test_%SYSFUNC(TODAY(),DATE9.).XLSX";

ODS EXCEL FILE = OT ;
PROC PRINT DATA=SASHELP.CLASS;
RUN;

ODS GRAPHICS OFF;
ODS EXCEL CLOSE;

I tried to run the above STP from numerous Apps (STP web app, Web report studio, information delivery portal, EG etc), it all seems normal to me.

 

SAS Super FREQ
Posts: 8,742

Re: ODS EXCEL in Web StoreProcess

Hi:

   I have 2 suggestions: 1) post this question in the Stored Process forum and 2) review this Tech Support note: http://support.sas.com/kb/32/634.html. Notice that in the Tech Support note, they use the _WEBOUT form of the stored process invocation and they do NOT use %STPBEGIN/%STPEND. So aside from any problems of the fact that you did not say WHERE you were invoking this stored process (EG, Web Report Studio, Add-in for Office, Info Delivery Portal, Stored Process Web App), you need to be aware of the fact that only certain types of output can be returned to the various client applications. Generally, Excel can only receive CSV, HTML or SASReportXML output from a stored process. You would need to check with Tech Support about whether the "flavor" of .XLSX file XML that ODS EXCEL creates can even be returned to Excel in a Stored Process. 

 

  It used to be that for ODS TAGSETS.EXCELXP the only client applications that could receive the Spreadsheet Markup Language XML were the Stored Process Web App and the Information Delivery Portal.

 

  You might want to search for previous forum postings in the Stored Process Forum, because I know there have been other postings about using _WEBOUT and the STPSRV_HEADER functions to change the content-type header.

 

cynthia

Solution
‎05-18-2016 05:30 AM
SAS Employee
Posts: 2

Re: ODS EXCEL in Web StoreProcess

Thanks for Your suggestions. I've found solution. Here's working code:

*ProcessBody;

data _null_;
	rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	rc = stpsrv_header('Content-disposition','attachment; filename=test.xlsx');
run;

ods excel file = _webout ;
ods excel style = Plateau;

proc print noobs label data=sashelp.TOURISM; run;

ods excel close;

This store process is invoking by Stored Process Web App.

PROC Star
Posts: 1,230

Re: ODS EXCEL in Web StoreProcess

I know it's an old post, but I agree it would be nice if %STPBEGIN() could be updated to know about the ODS EXCEL desitination.  I assume it would probably update this block:

 

%*;
%*  Map ODS destination to file type.  Use HTML as a
%*  default file type.
%*;

%if (%qupcase(%qcmpres(&_ODSDEST)) eq PDF) or
    (%qupcase(%qcmpres(&_ODSDEST)) eq PRINTER PDF) %then
  %let FILETYPE=pdf;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq PS) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq PRINTER PS) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq PRINTER POSTSCRIPT) %then
  %let FILETYPE=ps;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq CSV) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.CSV) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq CSVALL) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.CSVALL) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.CSVBYLINE) %then
  %let FILETYPE=csv;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq XML) or
          (%index(%qupcase(&_ODSDEST),SASREPORT) eq 1) or
          (%index(%qupcase(&_ODSDEST),TAGSETS.SASREPORT) eq 1) %then
  %let FILETYPE=xml;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq RTF) %then
  %let FILETYPE=rtf;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq WML) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.WML) %then
  %let FILETYPE=wml;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq LATEX) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.LATEX) %then
  %let FILETYPE=tex;
%else
  %let FILETYPE=html;

 

Of course the beauty of it being an autocall macro is that you're free to muck with it yourself (at your own risk : ).  My use case was silghtly different, but had similar problem of %STPBEGIN() adding path= which caused ODS EXCEL statement to error. 

 

Not wanting to change much, I only updated the section of %STPBEGIN() which adds the path option so it doesn't do it when &_ODSDEST=EXCEL

%*;
%*  Set the PATH option for streaming if
%*  not already set by caller.
%*;

%if (%qupcase(%qsubstr(%qcmpres(&_RESULT),1,6)) eq STREAM) and
    (&PATHFLAG eq 0) and
    (%qupcase(%qcmpres(&FILETYPE)) ne PS) and
    (%qupcase(%qcmpres(&FILETYPE)) ne PDF)
    and (%qupcase(%qcmpres(&_ODSDEST)) ne EXCEL) /* new line*/
%then %do;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 924 views
  • 0 likes
  • 4 in conversation