The SAS Output Delivery System and reporting techniques

Does excel destination use only one by variable with the sheet_interval='bygroup' option?

Reply
Highlighted
Regular Contributor
Posts: 179

Does excel destination use only one by variable with the sheet_interval='bygroup' option?

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

Super User
Posts: 9,865

Re: Does excel destination use only one by variable with the sheet_interval='bygroup' option?

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;
Regular Contributor
Posts: 179

Re: Does excel destination use only one by variable with the sheet_interval='bygroup' option?

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

SAS Super FREQ
Posts: 8,818

Re: Does excel destination use only one by variable with the sheet_interval='bygroup' option?

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:

byvars_mult_shet.png

 

cynthia

Regular Contributor
Posts: 179

Re: Does excel destination use only one by variable with the sheet_interval='bygroup' option?

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

SAS Super FREQ
Posts: 8,818

Re: Does excel destination use only one by variable with the sheet_interval='bygroup' option?

Hi, Ben:
Probably so, I did not understand the requirement to have multiple procedures on the same sheet -- but I don't think that BY group processing. This previous post explains needing a dummy table to "reset" ODS EXCEL so you can use NONE for sheet_interval with multiple outputs on multiple sheets, but the example might not be completely relevant -- since they are not using BY group processing and I assume that both of your steps will have BY group processing?

My suspicion is you might have to "macroize" your code to mimic by group processing for each procedure.

cynthia
Regular Contributor
Posts: 179

Re: Does excel destination use only one by variable with the sheet_interval='bygroup' option?

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. Smiley Happy

 

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

Ask a Question
Discussion stats
  • 6 replies
  • 311 views
  • 6 likes
  • 3 in conversation