09-14-2017 12:50 PM
Am playing around with generating multiple tabs in a spreadsheet when using multiple by variables. 9.4M4 version.
Running the following code:
ods excel file="c:\temp\sh.xlsx" options(sheet_interval="bygroup" sheet_name='none');
proc report data=sashelp.shoes;
by region subsidiary ;
column product stores sales;
ods excel close;
The region comes out on the tab but not the subsidiary. Does it only handle one by variable?
09-15-2017 10:02 AM
Try this one . options nobyline; ods excel file="c:\temp\sh.xlsx" options(sheet_interval="bygroup" sheet_name='#byval1 #byval2'); proc report data=sashelp.shoes; by region subsidiary ; column product stores sales; run; ods excel close;
09-15-2017 11:25 AM
I had tried that approach initially; if you look at the output, you will see the first tab is named 'Africa Addis Ababa'. Based on the sample data, the next one should be called 'Africa Algiers'. Instead it picks up the 2nd region instead with the 1st subsidiary as the next part of the tab's name. And each tab has all the subsidiaries on it for that region.
I was hoping it would split out the tabs by region/subsidiary.
Of course the obvious workaround is to create a temp variable with the values concatenated together and use that. Gets a bit awkward though if the variables are not all the same type (character & numeric).
09-15-2017 06:58 PM
I wasn't sure that this would work, but it did:
proc sort data=sashelp.class out=class; by sex age; run; options nobyline; ods excel file="c:\temp\test_bygrp.xlsx" options(sheet_interval="table" sheet_name='#byval1 #byval2'); proc report data=class; by sex age ; column name sex age height weight; run; ods excel close;
here's the output on one of the sheets but you can see the tabs:
09-18-2017 03:48 PM
Interesting. that approach does work. Unfortunately the "table" option prevents multiple tables with the same by values from appearing on the same page (say having 2 procs generating output).
The docs on bygroup seem to suggest it should work with the sheet_interval you used. Should I loop T.S. into this?
09-18-2017 05:34 PM
10-11-2017 04:28 PM
Sorry for the delay; apparently the root cause of the issue I was having is that Excel has a 31 character length limit on tab names, and SAS reserves a few of those, shrinking it down to somewhere around 28. The data I had in the BY statement creating these things was at times exceeding this. The warning system advising of this condition is apparently stealth mode.
Vince gave his ODS Excel Part 2 talk to the ValSUG group recently where I brought up this problem and it got resolved in a much cleaner way, with a pretty ToC page and much shorter tab names.