The SAS Output Delivery System and reporting techniques

tagsets.excelXP - summary sheet_name='All', rest of sheets with byvar val

Reply
Occasional Contributor
Posts: 9

tagsets.excelXP - summary sheet_name='All', rest of sheets with byvar val

I’m having difficulty creating the 1st summary sheet with a specific name, and then automatically naming subsequent sheets with the BY variable value. If I don’t include the summary sheet, the BY sheets are as desired, F & M, but it seems if I’ve specified a sheet_name value once, that’s the one that’s used for a subsequent sheets. Or, if I specify a blank sheet_name in the 2nd ODS statement, the subsequent sheets are numbered consecutively, 2 & 3 and are not named with the BY variable value.

How do I get three sheets, first one “All”, second “F”, third “M” ? I downloaded the latest tagset, no difference.

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12).

title 'Want 1st sheet All, rest to be bygroups';

ods listing close;
ods tagsets.excelxp
file = "c:\temp\tagsets_sheetname.xls"
style = seaside
options ( embedded_titles = 'Yes'
sheet_name = 'All'
sheet_interval = 'bygroup'
sheet_label = ' '
suppress_bylines = 'yes'
)
;

proc print data = sashelp.class noobs;
run;

ods tagsets.excelxp
options ( sheet_interval = 'bygroup'
sheet_label = ' '
sheet_name = ' '
suppress_bylines = 'yes'
)
;

proc sort data = sashelp.class
out = class;
by sex;
run;

proc print data = class noobs;
by sex;
run;

ods tagsets.excelxp close;
ods listing;
Respected Advisor
Posts: 3,777

Re: tagsets.excelXP - summary sheet_name='All', rest of sheets with byvar val

See if the modification works for you

[pre]
ods tagsets.excelxp
options ( sheet_interval = 'bygroup'
sheet_label = ' '
sheet_name = 'none'

suppress_bylines = 'yes'
)
;
[/pre]
Occasional Contributor
Posts: 9

Re: tagsets.excelXP - summary sheet_name='All', rest of sheets with byvar val

Bingo ! Thanks.
Valued Guide
Posts: 2,174

Re: tagsets.excelXP - summary sheet_name='All', rest of sheets with byvar val

That is almost perfect!
It shows the title line just once for each PROC so no title shows after the first by-group on the second PROC PRINT.
Is that because I'm working with excelXP v1.94?
Are later releases able to surface titles for every by-group?

peterC
Ask a Question
Discussion stats
  • 3 replies
  • 231 views
  • 0 likes
  • 3 in conversation