BookmarkSubscribeRSS Feed
BenConner
Pyrite | Level 9

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

6 REPLIES 6
Ksharp
Super User
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;
BenConner
Pyrite | Level 9

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

Cynthia_sas
SAS Super FREQ

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

BenConner
Pyrite | Level 9

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

Cynthia_sas
SAS Super FREQ
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
BenConner
Pyrite | Level 9

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1623 views
  • 6 likes
  • 3 in conversation