Excel multiple sheet output using Stored Process

[dipanarya1]

Hi Team,

We are trying to provide an options to generate the RTF, PDF and Excel file using STP.

We are able to generate an output in RTF and in PDF format, but while trying to generate the Excel file the STP is giving the _webout related error, attached log file and below is the code snippet.

/*Code Start*/

%let mcv_back = "<button class='NAV' onclick=history.go(-1)%str(;) title='Go Back 1 Page'><img src='/images/left.png' /></button>" ;
%let mcv_forward ="<button class='NAV' onclick=history.go(+1)%str(;) title='Go Forward 1 Page'><img src='/images/right.png' /></button>";
%let mcv_pdf = "<button class='NAV' onclick=location.href='do?_program=%sysfunc(urlencode(&_program.))&mcv_param.%str(&)_odsdest=PDF' title='Click to Download to PDF.' ><img src='/images/pd
f.png' /></button>";
%let mcv_excel = "<button class='NAV' onclick=location.href='do?_program=%sysfunc(urlencode(&_program.))&mcv_param.%str(&)_odsdest=EXCEL' title='Click to Download to Excel.'><img src='/images
/excel_small.png' /></button>";
%let mcv_word = "<button class='NAV' onclick=location.href='do?_program=%sysfunc(urlencode(&_program.))&mcv_param.%str(&)_odsdest=RTF' title='Click to Download to Word'><img src='/images/wor
d.png' /></button>";
%let mcv_print = "<button class='NAV' onclick=window.print(); title='Click to Print report.'><img src='/images/print.png' /></button>";
%let mcv_rerun = "<button class='NAV' onclick=window.location.reload(); title='Click to Re-Run report.'> <img src='/images/rerun.png' /></button>";
%let mcv_close = "<button class='NAV' onclick=self.close(); title='Click to Close window.'><img src='/images/close.png' /></button>";

%if %upcase(%superq(mcv_portal_only)) = YES /*and %superq(_result) = PACKAGE_TO_ARCHIVE*/ %then %do ;
%stpbegin ;
ods text = "^{style [fontsize=14pt]&c_stp_name.}" ;
ods text = "^{style [fontsize=14pt]Can only be viewed on the ERS Portal.}" ;
%end ;
%else %do ;

%if %upcase(%superq(_odsdest)) ne HTML and
%upcase(%superq(_odsdest)) > %str() %then %let mcv_dest = &_odsdest. ;

%if %upcase(%superq(mcv_dest)) = EXCEL %then %do ;

ODS _all_ close;

data _null_;
rc = appsrv_header('Content-type', 'application/vnd.ms-excel');
rc = appsrv_header('Content-disposition','attachment; filename="Class.xlsx"');
run;

* Create the file in a temporary location;

filename temp temp;

ods Excel file=temp style=HTMLBlue;
proc print data=sashelp.class; run; quit;
ods Excel close;

* Copy the file to _WEBOUT;

data _null_;
infile temp recfm=f lrecl=1;
file _webout recfm=n;
input;
put _infile_;
run;
%end ;
%else %let _odsdest = &mcv_dest. ;;
%if %upcase(%superq(mcv_dest)) = PANEL %then %let _odsdest = tagsets.htmlpanel ;
%if %upcase(%superq(mcv_dest)) = TABLE %then %let _odsdest = tagsets.tableeditor ;
%if %upcase(%superq(mcv_dest)) = SPECIAL %then %let _odsdest = tagsets.special ;

%if %upcase(%superq(mcv_dest)) =HTML OR %upcase(%superq(mcv_dest))=PANEL OR %upcase(%superq(mcv_dest))=TABLE OR %upcase(%superq(mcv_dest))=SPECIAL %then %do ;
%include "/apps/usr/ERS/MacroLibrary/tableeditor.tpl";
/* ods tagsets.tableeditor file=_webout style= &mcv_tag_options.;*/
%let _odsoptions = file=_webout (title='ERS') style= &mcv_style. &mcv_tag_options. ;

%end ;
%if %upcase(%superq(mcv_dest)) = PDF %then %do ;
%let _odsoptions = notoc &mcv_ods_options. ;
data _null_ ;
rc = stpsrv_header('Content-disposition',"attachment; filename=&mcv_stp_name..pdf") ;
run ;
%end;

[dipanarya1]

%macro mac_buttons_ods(
   mcv_style          = ERS,  /* Select the SAS style you want to use.  default = ers */
   mcv_dest           = HTML, /* PDF, RTF, XML, EXCEL, PANEL, SPECIAL, TEXT or TABLE */
   mcv_tag_options    = ,
   mcv_excel_options  = %str(options(sheet_label="ERS" sheet_interval="none" frozen_headers="yes" embedded_titles="yes")),
   mcv_ods_options  = %str(),
   mcv_title          = ERS, /* Enter the text you want to see in the title area of the browser window.*/
   mcv_portal_only    = no,
   mcv_mode           = PROD, /* PROD: mprint mlogic symbolgen will be turned off otherwise they will be turned on. */
   mcv_param          =,/*all the parameters to pass in url*/
   ) ;


   %global mcv_stp_name
           mcv_ers_url
           mcv_sasweb
           _odsdest _odsoptions _odsstyle _odsstylesheet _program _url mcv_filename
           mcv_back mcv_forward mcv_pdf mcv_excel mcv_word mcv_print mcv_rerun mcv_close
           ;
   %local mcv_style
          mcv_dest
          mcv_tag_options
          mcv_excel_options
          mcv_title
          mcv_ods_options
          mcv_portal_only
          mcv_mode ;

   *ProcessBody ;
   options minoperator ;

   %* Only use this macro if running as a stored process ;
   %if %superq(_program) = %then %return ;;


   libname ersstyle '/apps/usr/ERS/styles' access=readonly ;
   ods path ersstyle.templat(read) sasuser.templat(update) sashelp.tmplmst(read) ;
   ods escapechar = '^' ;

   %let _odsstyle   = &mcv_style. ;
   %let mcv_sasweb  = https://&_srvname.:&_srvport./ ;

   data _null_ ;
     call symputx('mcv_stp_name',scanq("&_program.",countc("&_program.","/"),'/')) ;
   run ;
 %let mcv_back = "<button class='NAV' onclick=history.go(-1)%str(;) title='Go Back 1 Page'><img src='/images/left.png' /></button>" ;
    %let mcv_forward ="<button class='NAV' onclick=history.go(+1)%str(;) title='Go Forward 1 Page'><img src='/images/right.png' /></button>";
    %let mcv_pdf     = "<button class='NAV' onclick=location.href='do?_program=%sysfunc(urlencode(&_program.))&mcv_param.%str(&)_odsdest=PDF' title='Click to Download to PDF.' ><img src='/images/pd
f.png' /></button>";
    %let mcv_excel   = "<button class='NAV' onclick=location.href='do?_program=%sysfunc(urlencode(&_program.))&mcv_param.%str(&)_odsdest=EXCEL' title='Click to Download to Excel.'><img src='/images
/excel_small.png' /></button>";
    %let mcv_word   =  "<button class='NAV' onclick=location.href='do?_program=%sysfunc(urlencode(&_program.))&mcv_param.%str(&)_odsdest=RTF' title='Click to Download to Word'><img src='/images/wor
d.png' /></button>";
    %let mcv_print   = "<button class='NAV' onclick=window.print(); title='Click to Print report.'><img src='/images/print.png' /></button>";
    %let mcv_rerun   =  "<button class='NAV' onclick=window.location.reload(); title='Click to Re-Run report.'> <img src='/images/rerun.png' /></button>";
    %let mcv_close   = "<button class='NAV' onclick=self.close(); title='Click to Close window.'><img src='/images/close.png' /></button>";


   %if %upcase(%superq(mcv_portal_only)) = YES /*and %superq(_result) = PACKAGE_TO_ARCHIVE*/ %then %do ;
         %stpbegin ;
         ods text = "^{style [fontsize=14pt]&c_stp_name.}" ;
         ods text = "^{style [fontsize=14pt]Can only be viewed on the ERS Portal.}" ;
   %end ;
   %else %do ;

      %if %upcase(%superq(_odsdest)) ne HTML and
%upcase(%superq(_odsdest)) > %str() %then %let mcv_dest = &_odsdest. ;;

    %if %upcase(%superq(mcv_dest)) = EXCEL %then %do ;

ODS _all_ close;

data _null_;
rc = appsrv_header('Content-type', 'application/vnd.ms-excel');
rc = appsrv_header('Content-disposition','attachment; filename="Class.xlsx"');
run;

*  Create the file in a temporary location;

filename temp temp;

ods Excel file=temp style=HTMLBlue;
  proc print data=sashelp.class; run; quit;
ods Excel close;

*  Copy the file to _WEBOUT;

data _null_;
infile temp recfm=f lrecl=1;
file _webout recfm=n;
input;
put _infile_;
run;
      %end ;
      %else %let _odsdest = &mcv_dest. ;;
       %if %upcase(%superq(mcv_dest)) = PANEL   %then %let _odsdest = tagsets.htmlpanel ;
       %if %upcase(%superq(mcv_dest)) = TABLE   %then %let _odsdest = tagsets.tableeditor ;
      %if %upcase(%superq(mcv_dest)) = SPECIAL %then %let _odsdest = tagsets.special ;

      %if %upcase(%superq(mcv_dest)) =HTML OR  %upcase(%superq(mcv_dest))=PANEL OR %upcase(%superq(mcv_dest))=TABLE OR %upcase(%superq(mcv_dest))=SPECIAL %then %do ;
       %include "/apps/usr/ERS/MacroLibrary/tableeditor.tpl";
/*         ods tagsets.tableeditor file=_webout style= &mcv_tag_options.;*/
         %let _odsoptions = file=_webout (title='ERS') style= &mcv_style. &mcv_tag_options. ;

      %end ;
      %if %upcase(%superq(mcv_dest)) = PDF %then %do ;
         %let _odsoptions = notoc &mcv_ods_options. ;
         data _null_ ;
            rc = stpsrv_header('Content-disposition',"attachment; filename=&mcv_stp_name..pdf") ;
         run ;
      %end;
/* %if %upcase(%superq(mcv_dest)) = EXCEL %then %do ;
 %let _odsoptions = file=_webout ;
    data _null_;
      rc=stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      rc=stpsrv_header('Content-disposition','attachment; filename=try3.xlsx');
    run;
    proc print data=sashelp.cars;
    run;
      %end ;
 */     %if %upcase(%superq(mcv_dest)) = RTF %then %do ;
          %let _ODSOPTIONS= KEEPN BODYTITLE ;

         data _null_ ;
            rc = stpsrv_header('Content-disposition',"attachment; filename=&mcv_stp_name..doc") ;
         run ;

      %end ;
/*      %if %upcase(%superq(mcv_dest)) ne EXCEL %then %stpbegin ;;*/

   %end ;

   %if %upcase(&mcv_mode) ne PROD %then options mlogic mprint symbolgen ;
   %else                                options nomlogic nomprint nosymbolgen ;;
%mend mac_buttons_ods;

[Madelyn_SAS]

You posted this to the Web Report Studio community but it is not entirely clear if you are actually running this stored process in Web Report Studio. If you are, then I would suggest, instead, running the stored process in the SAS Stored Process Web Application. Web Report Studio can render stored processes only if they conform to a specific set of rules as described here:

http://support.sas.com/kb/47/839.html

[dipanarya1]

Hi @Madelyn_SAS,

We are able to run the SAS stored process in WRS and able to generate output in RTF and PDF output using the same SAS code here attached in my second post.

You can check the log for an error (attached in my first post), same code is working fine to generate PDF and RTF file but giving an error related to _webout(please check log for reference).

[Madelyn_SAS]

Web Report Studio does not support the code you are using to create the Excel file. You will need to use the SAS Stored Process Web Application. 

[dipanarya1]

Hi @Madelyn_SAS,

We are using the same stored process and calling this macro which I attached.

Please let me know if you need any more information