Good morning,
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;
run;
ods excel close;
The region comes out on the tab but not the subsidiary. Does it only handle one by variable?
Thanks!
--Ben
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;
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).
Thanks!
--Ben
Hi:
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:
cynthia
Hi Cynthia,
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?
--Ben
Hi Cynthia,
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.
Thanks!
--Ben
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.