The SAS Output Delivery System and reporting techniques

ODS TAGSETS.EXCELXP with Proc Tabulate and the indent function

Occasional Contributor
Posts: 14

ODS TAGSETS.EXCELXP with Proc Tabulate and the indent function

Is there a special feature with ODS EXCELXP that when using a Proc Tabulate the Indent= option is retained? When I use ODS to output to an Excel Spreadsheet and I use the INDENT=2 (for example) option, what should have been indented 2 spaces is placed in next available Cell. Any help would be greatly appreciated.


Posts: 9,371

Re: ODS TAGSETS.EXCELXP with Proc Tabulate and the indent function

Posted in reply to RodTennant
The INDENT= option works differently for most ODS destinations, than it does for the LISTING destination:
[quote from the doc]
In the HTML, RTF, and Printer destinations, the INDENT= option suppresses the row headings for class variables but does not indent nested row headings.

If you run this demo program (modified from the doc to use SASHELP.PRDSALE), you will see that none of the ODS destinations except for LISTING use INDENT=. (Same as the fact that NOSEPS is a LISTING only option.) All the other destinations, including TAGSETS.EXCELXP put the DIVISION rowheader information into a new column to the right of the REGION rowheader information.

There is an ODS style attribute called INDENT that works for RTF, PDF and HTML, where INDENT= is used in PROC REPORT to simulate the type of report indenting that you get with TABULATE. However, this option does not seem to be honored by TAGSETS.EXCELXP. I don't know whether there's any way to force Excel to indent when you use TAGSETS.EXCELXP...that would be a question for Tech Support.

options nodate pageno=1 linesize=80 pagesize=60;
ods listing;
ods html file='use_indent.html' style=sasweb;
ods rtf file='use_indent.rtf';
ods pdf file='use_indent.pdf';
ods tagsets.excelxp file='use_indent.xls' style=sasweb;
proc tabulate data=sashelp.prdsale format=dollar12. noseps;
title '1) Use INDENT= TABLE option in TABULATE to see behavior in other ODS destinations';
class region division prodtype;
var actual;
table region*division,
prodtype='ProdType'*actual=' '*sum=' '
/ rts=25 indent=4;
ods _all_ close;

ods html file='use_report.html' style=sasweb;
ods rtf file='use_report.rtf';
ods pdf file='use_report.pdf';
ods tagsets.excelxp file='use_report.xls' style=sasweb;

proc report data=sashelp.prdsale nowd;
title '2) Use INDENT= style attribute with PROC REPORT';
column region division prodtype,actual;
define region / group noprint ;
define division / group
style(column)={indent=.5in cellwidth=1.5in}
define prodtype / across;
define actual / sum;
compute before region / style={just=l};
line Region $25.;
ods _all_ close;[/pre]
Occasional Contributor
Posts: 14

Re: ODS TAGSETS.EXCELXP with Proc Tabulate and the indent function

Posted in reply to RodTennant
Thank you Cynthia...I appreciate your time and value your input.

Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation