The SAS Output Delivery System and reporting techniques

ODS TAGSETS.EXCELXP with Proc Tabulate and the indent function

Reply
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.

Thanks

Rod
SAS Super FREQ
Posts: 8,740

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

Hi:
The INDENT= option works differently for most ODS destinations, than it does for the LISTING destination:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473740.htm#a003068110
[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.
[endquote]

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.

cynthia
[pre]
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;
run;
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}
style(header)={cellwidth=1.5in};
define prodtype / across;
define actual / sum;
compute before region / style={just=l};
line Region $25.;
endcomp;
run;
ods _all_ close;[/pre]
Occasional Contributor
Posts: 14

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

Thank you Cynthia...I appreciate your time and value your input.

Rod
Ask a Question
Discussion stats
  • 2 replies
  • 350 views
  • 0 likes
  • 2 in conversation