topic Re: tagsets.excelXP - summary sheet_name='All', rest of sheets with byvar val in ODS and Base Reporting

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

Harry — Thu, 17 Feb 2011 16:32:10 GMT

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;

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

data_null__ — Thu, 17 Feb 2011 16:52:48 GMT

See if the modification works for you

[pre]
ods tagsets.excelxp
options ( sheet_interval = 'bygroup'
sheet_label = ' '
sheet_name = 'none'
suppress_bylines = 'yes'
)
;
[/pre]

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

Harry — Thu, 17 Feb 2011 18:31:53 GMT

Bingo ! Thanks.

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

Peter_C — Fri, 18 Feb 2011 11:58:19 GMT

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