Stored process with multiple output modes (pdf, excel, html)

[jlwatts]

We have some sas code that we %include into most of our stored processes so that users can select their preferred style of output.  It is:

data _null_;
if upcase("&outtype") = "HTML" then call symput("odsdest","HTML");
if upcase("&outtype") = "PDF" then do;
options orientation=landscape; rc = stpsrv_header('Content-type','application/pdf');
rc = stpsrv_header('Content-disposition','attachment; filename=temp.pdf');
call symput("odsdest","PDF");
end;
if upcase("&outtype") = "EXCEL" then do;
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header('Content-disposition','attachment; filename=temp.xls');
call symput("odsdest","tagsets.excelxp");
end;
run;

%let _odsdest=&odsdest;

%global _ODSSTYLE;
%let _ODSSTYLE=Normal;
run;

The HTML and PDF output is fine, as the PROC REPORT is output with the headers and footers.  But the excel version of the output is lacking the headers and footers.  There are two parts to the report with each part being written to a separate worksheet in the file.  

Why are the headers/footers not being displaced in Excel?

I tried a different set of code that does not have  call symput("odsdest","tagsets.excelxp");

That output puts the headers/footers, but it all appears on a single worksheet.

Suggestions?

[Quentin]

You haven't shown the code that generates the report.  I assume it's inside a %stpbegin  block?

If you're using tagsets.excelxp the default is for titles and footnotes to go in the header/footer of the sheet.  Did you check there to see if they exist?

If you want to see the titles/footnotes in a cell in the spreadsheet, you can use the embedded_titles='yes' option. This is an option of tagsets.excelxp destination.

I think the way to do this would be to add to your excel block to create a global macro variable _odsoptions

if upcase("&outtype") = "EXCEL" then do;
  rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
  rc = stpsrv_header('Content-disposition','attachment; filename=temp.xls');
  call symput("odsdest","tagsets.excelxp");
  call symput("_odsoptions", "embedded_titles='yes' embedded_footers='yes'");  *new line;
end;

This paper from the great Cynthia Zender explains the use of the global macro vars _odsoptions, _odsdstyle, etc:

https://www.lexjansen.com/nesug/nesug07/ap/ap22.pdf

That said, tagsets.excelxp is pretty much deprecated, SAS introduced ODS EXCEL destination which will write .xlsx files 10 (?) years ago.  You might want to switch to ODS EXCEL.  But the general issue will likely be the same, you'll need to tinker with destination-specific options to get what you want. For comparison of tagsets.excelxp and excel destination, see this paper by the great Chevell Parker:
https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf

hope that helps

[jlwatts]

Thank you. I will read the paper.