topic Does excel destination use only one by variable with the sheet_interval='bygroup' option? in ODS and Base Reporting

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

BenConner — Thu, 14 Sep 2017 16:50:53 GMT

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

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

Ksharp — Fri, 15 Sep 2017 14:02:44 GMT

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;

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

BenConner — Fri, 15 Sep 2017 15:25:51 GMT

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

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

Cynthia_sas — Fri, 15 Sep 2017 22:58:23 GMT

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

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

BenConner — Mon, 18 Sep 2017 19:48:39 GMT

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

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

Cynthia_sas — Mon, 18 Sep 2017 21:34:54 GMT

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

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

BenConner — Wed, 11 Oct 2017 20:28:53 GMT

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