how to place the text note for the 2nd tab correctly using ods excel with proc report

[JeffSAS]

I need to generate a report with 2 tabs, and each tab should have a text note. however, the following code gave me a different result -

there are 2 questions:

1. I used the following code but both the text notes are show up in the 1st tab(see the 1st and 2nd screenshot).

2. the subtotal(i.e. filtered total by sex) does not work (please see the 3rd screenshot)

proc sql noprint;select sum(age),sum(height),sum(weight), sum(height)/sum(weight) format=percent8.2 into :s1,:s2,:s3,:ratio from sashelp.class; quit;
%put s1=&s1. s2=&s2. s3=&s3. ratio=&ratio.;
*tab1;;
ods excel close;
 ods excel file="/sasdata/mi/rpma/rd/m16563t/Auto_Report/data/class1.xlsx" options(sheet_name="tab1" autofilter='1-2'  SHEET_INTERVAL='PAGE');
    ods escapechar='^';
   ods text=" total age is  |     total hight is      |  total weight is   | %% ratio ";
   ods text=  " ^_^_  &s1.  |         &s2.    |     &s3.   |   &ratio.%% ";              *  ^_    %;

proc report data=sashelp.class  nocenter style(header)={just=l};
  column name sex age height weight ratio;
  define name -- weight/ display;
  define age  / analysis sum format=comma12. style(column)= [cellwidth=.5in];
  define height / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column;
  define weight / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column;
  define Ratio / computed format=percent8.2;
   compute ratio;
      ratio = height.sum / weight.sum;
   endcomp;
rbreak before / summarize style=[background=lightblue font_weight=bold];* CONTENTS='Total';
  compute before;
    call define(age,   'style',"style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']");
    call define(height,'style',"style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); 
    call define(weight,'style',"style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); 
    call define(Ratio, 'style',"style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']");  
  endcomp;
run;

*tab2;;
ods excel options(sheet_name="tab2" autofilter='1-2'  sheet_interval='now' SHEET_INTERVAL='PAGE');
    ods escapechar='^';
   ods text=" total age is  |     total hight is      |  total weight is   | %% ratio ";
   ods text=  " ^_^_  &s1.  |         &s2.    |     &s3.   |   &ratio.%% ";              *  ^_    %;
proc report data=sashelp.class  nocenter style(header)={just=l};
  column name sex age height weight ratio;
  define name -- weight/ display;
  define age  / analysis sum format=comma12. style(column)= [cellwidth=.5in];
  define height / analysis sum format=comma12. style(column)= [cellwidth=.8in];
  define weight / analysis sum format=comma12. style(column)= [cellwidth=.8in];
  define Ratio / computed format=percent8.2;
   compute ratio;
      ratio = height.sum / weight.sum;
   endcomp;
rbreak before / summarize style=[background=lightblue font_weight=bold];
  compute before;
    call define(age,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']");
    call define(height,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); 
    call define(weight,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); 
    call define(Ratio,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']");  
  endcomp;
run;

title;
ods excel close;

and results are:

[ballardw]

One comment:

ods excel options(sheet_name="tab2" autofilter='1-2'  sheet_interval='now' SHEET_INTERVAL='PAGE');

In many places when you have two different values of the same option SAS typically only applies one and often the last encountered. 

You might try a seperate options with just the NOW and if your really need PAGE a separate ods excel options(sheet_interval='PAGE'); following.

Click on the cells where the Row 4 data does not change. You will see that you have placed values into the cell, not a formula. I can't say what the Tagattr would look like for the formula though, I don't play with much Excel specific output.

[JeffSAS]

ods excel options(sheet_name="tab2" autofilter='1-2'  sheet_interval='now' );

does not work.

[Cynthia_sas]

Hi:

  Also, in terms of PROC REPORT syntax...there's no point in having this statement

  define name -- weight/ display;

because then you turn around and define age, height and weight as ANALYSIS SUM and those are the usages that will be used, NOT the DISPLAY usage. An item in PROC REPORT can only have 1 usage -- DISPLAY or ANALYSIS or ORDER or GROUP or COMPUTED. So listing all of them the way you do serves no purpose.

  My tendency is to ask why you're using ODS TEXT and not just using COMPUTE BEFORE _PAGE_ with a LINE statement. Also, I'm not sure whether you are intending or trying to the pipe symbols to line up so the top report looks like a mini-table, but with ODS destinations, usually attempts to line things up with extra spaces doesn't work.

  I modified your code a bit. There are 2 approaches I can see -- using the COMPUTE BEFORE _PAGE_ approach (but honestly, it seems redundant with the RBREAK BEFORE that you've got. The other approach is switching to PROC ODSTEXT. The options and suboptions you'd use would be different.

  For my test, I simplified your code and got rid of autofilter and got rid of formulas because they did not seem relevant to your question. I also numbered the strings so you could see 1a and 1b above the table on tab1 and see 2a and 2b above the table on tab2.

Cynthia

COMPUTE BEFORE approach:

proc sql noprint;
  select sum(age),sum(height),sum(weight), sum(height)/sum(weight) format=percent8.2 
     into :s1,:s2,:s3,:ratio 
  from sashelp.class; 
quit;
%put &=s1  &=s2  &=s3 &=ratio;

*tab1;
 ods excel file="c:\temp\use_compute_before_page.xlsx" 
     options(sheet_name="tab1" sheet_interval='Output');
   
 proc report data=sashelp.class  nocenter style(header)={just=l};
  column name sex age height weight ratio;
  define name / display;
  define sex/ display;
  define age  / analysis sum format=comma12. style(column)= [cellwidth=.5in];
  define height / analysis sum format=comma12.1 style(column)= [cellwidth=.8in];
  define weight / analysis sum format=comma12.1 style(column)= [cellwidth=.8in];
  define Ratio / computed format=percent9.2;
  compute ratio;
      ratio = height.sum / weight.sum;
  endcomp;
  rbreak before / summarize style=[background=lightblue font_weight=bold];* CONTENTS='Total';
  compute before _page_ / style={just=l font_weight=bold};
    line "1a) total age is  |     total height is      |  total weight is   | ratio ";
	line " " ;
    line "1b)  &s1.  |         &s2.    |     &s3.   |   &ratio. " ;
  endcomp;
run;


*tab2;
ods excel options(sheet_name="tab2" );
  
 proc report data=sashelp.class  nocenter style(header)={just=l};
  column name sex age height weight ratio;
  define name / display;
  define sex/ display;
  define age  / analysis sum format=comma12. style(column)= [cellwidth=.5in];
  define height / analysis sum format=comma12.1 style(column)= [cellwidth=.8in];
  define weight / analysis sum format=comma12.1 style(column)= [cellwidth=.8in];
  define Ratio / computed format=percent9.2;
  compute ratio;
      ratio = height.sum / weight.sum;
  endcomp;
  rbreak before / summarize style=[background=lightblue font_weight=bold];
  compute before _page_ / style={just=l font_weight=bold};
    line "2a) total age is  |     total height is      |  total weight is   |  ratio " ;
    line " " ;
    line "2b)   &s1.  |         &s2.    |     &s3.   |   &ratio. " ;    
  endcomp;
 run;

title;
ods excel close;

PROC ODSTEXT approach:

proc sql noprint;
  select sum(age),sum(height),sum(weight), sum(height)/sum(weight) format=percent8.2 
     into :s1,:s2,:s3,:ratio 
  from sashelp.class; 
quit;
%put &=s1  &=s2  &=s3 &=ratio;

*tab1;
 ods excel file="c:\temp\use_proc_odstext.xlsx" 
     options(sheet_name="tab1" sheet_interval='NONE');
   
proc odstext pagebreak=NO;
    p "1a) total age is  |     total height is      |  total weight is   |  ratio "  / style={just=l font_weight=bold};
	p " " / style={just=l font_weight=bold};
    p "1b)  &s1.  |         &s2.    |     &s3.   |   &ratio. " / style={just=l font_weight=bold};
run;
  
proc report data=sashelp.class  nocenter style(header)={just=l};
  column name sex age height weight ratio;
  define name / display;
  define sex/ display;
  define age  / analysis sum format=comma12. style(column)= [cellwidth=.5in];
  define height / analysis sum format=comma12.1 style(column)= [cellwidth=.8in];
  define weight / analysis sum format=comma12.1 style(column)= [cellwidth=.8in];
  define Ratio / computed format=percent9.2;
  compute ratio;
      ratio = height.sum / weight.sum;
  endcomp;
  rbreak before / summarize style=[background=lightblue font_weight=bold];* CONTENTS='Total';
run;

** force a break before the next sheet with a dummy DATA step;
ods excel options(sheet_interval='Now');
title1;   
ods exclude all;
data _null_;
   declare odsout obj();
run;
ods select all;
 

*tab2;
ods excel options(sheet_name="tab2" sheet_interval='None');
   
proc odstext pagebreak=NO;
   p "2a) total age is  |     total height is      |  total weight is   |  ratio "  / style={just=l font_weight=bold};
   p " " / style={just=l font_weight=bold};
   p "2b)   &s1.  |         &s2.    |     &s3.   |   &ratio. "  / style={just=l font_weight=bold};    
run;

proc report data=sashelp.class  nocenter style(header)={just=l};
  column name sex age height weight ratio;
  define name / display;
  define sex/ display;
  define age  / analysis sum format=comma12. style(column)= [cellwidth=.5in];
  define height / analysis sum format=comma12.1 style(column)= [cellwidth=.8in];
  define weight / analysis sum format=comma12.1 style(column)= [cellwidth=.8in];
  define Ratio / computed format=percent9.2;
  compute ratio;
      ratio = height.sum / weight.sum;
  endcomp;
  rbreak before / summarize style=[background=lightblue font_weight=bold];
 run;

title;
ods excel close;

[JeffSAS]

1. I tried line statement. the text notes is a little long, and it wrapped into several row, and wonder if you solution to control the format/length.

2. proc odstext works well.

3. do you have any solution for the filtered total issue (the 2nd question)?

anyway, thanks and appreciate!

[Cynthia_sas]

Hi:
I think the problem with the LINE statement approach is that you are narrowing the table by specifying small widths for some of the columns. So the LINE statement text will want to wrap to fit the boundaries of the table. My approach would be to make the table wider by taking the width off of the DEFINE statements and use style(report)={width=5in} -- which would probably give the LINE text enough room and would cause the report columns to "spread" wider to fit the new table boundaries.
I don't work with Excel formulas enough to feel comfortable commenting on the formula you specified. It looks to me like you're trying to change the summary statistics that PROC REPORT calculated for the RBREAK with your formula and since I would never try to do it and probably more importantly, I don't know enough Excel formulas to really grasp your formula, if I can't understand, test, explain or change the formula, I wouldn't presume to comment on why it might not be working.
Cynthia