Proc Report Custom Grand Total

[sasiscool]

I have a proc report output that looks likes this:

                              Year1          Year2

Group1   Apple        3               4

               Berry        1                2

subtotal                  4                 6

Group2   Apple        7               9

               Berry        2              3

subtotal                   9              12

group3    apple       1                2

               berry         5               1

subtotal                   6                3

grandtotal              X                X

The code looks like this

proc report data=x t1 out=test


define report_grp / group;
define fruit/ group format=$ftpt. ' ';
define term_desc / across ' ';

break after report_grp /summarize ;

rbreak after / summarize;

Now, i am trying to force the grand total rbreak to repeat the subtotal of group1 ( which is 4 for Year 1 and 6 for Year 2).

I could manually enter it like:

compute after;
if report_grp=. then
_C3_=4;

_C4_=6;
endcomp;

or something similar with the _BREAK_ variable. However, I have to do this for several reports, and do not want to manually change a bunch of columns.

 I have tried to think of a nifty way to just repeat that first subtotal line for the grand total, but I keep coming up short. I feel it is possible, but need some direction.

Thanks!!!

[Cynthia_sas]

Hi:
I guess I don't understand why you would want to repeat the first group on the rbreak, that doesn't make sense unless somehow you have presummarized your data and Group1 isn't really Group1, but is the grand total (still doesn't make sense given the numbers you show). Of course, if you want the grand total both before and after the report, that's possible. The correct grand total for the output you show is 19 for year 1 and 21 for year 2. The only way to do the adjustment you want is to do what you're doing -- but I would not test for report_grp = ., it would be better to test for
if _break_ = '_RBREAK_' then .... (because the ONLY time when the automatic _BREAK_ value will be _RBREAK_ is on the last summary row of the report. And, besides, report_grp looks like a character variable, so it will never be a numeric missing (or .).

cynthia

[sasiscool]

thank you so much Cynthia!

Sorry in the haste of annonymizing my data, I should have been more careful. This table is more accurate

                              Year1          Year2

Group1   Apple        9               11

               Berry        7                4

subtotal                  16                 15

Group2   Apple        7               9

               Berry        2              3

subtotal                   9              12

group3    apple       1                2

               berry         5               1

subtotal                   6                3

grandtotal              X                X

There are three distinct groups. Group1 is, in fact, a total of group 2 and group 3. But, it is its own group and not a calculated variable or anything. Group 2 and Group3 are their own distinc groups, but if you add them, they equal group 1. Unfortunately, for the purposes of the report I am making, I need to have it look just like that--First column being group, sencond fruit, etc.  So, I can't just include an rbreak and only show group 2 and 3, since it won't break out each row for the fruit. ( I hope i am not being confusing!)

All I need is to get this subtotal:

" subtotal                  16                 15"  

to also appear again at the bottom as a grandtotal.  I just can't think of a way to get to 'call' that row  since proc report doesn't work that way.  Maybe something with dummy variable ?

[Cynthia_sas]

Hi:
  Unfortunately, once a row has been written to the report, PROC REPORT is done with it. So once PROC REPORT has written the row for GROUP1's summary line, then that row is done. PROC REPORT cannot call it back again at the bottom of the report. You could save the values in temporary variables or in macro variables, but there's nothing automatic that PROC REPORT can do to get you that value. This is the downfall of trying to pre-summarize your data. Depending on your destination of interest, It might be easier to just make 2 tables and then you don't need dummy variables, macro variables or anything else:

cynthia

[sasiscool]

oh well. thank you for your suggestions!

[ballardw]

Realizing that your actual report may be more complex a limited example of this is possible with a multilabel format and Proc tabulate.

I suspect that this does not match your existing data layout as it sounds like you have done some summary to create your "group 1" ahead of time and appended data.

The following approach might be able to avoid that presumed summary step.

proc format library=work;
value grp (multilabel notsorted)
1,2 = "Group 1"
1   = "Group 2"
2   = "Group 3"
;
run;

data example;
   do year= 2012,2013;
      do i = 1 to (ceil(20*Ranuni(123)));
         group = rand('table',1/3,2/3);
         output;
      end;
   end;
run;

proc tabulate data=example;
   class group / mlf preloadfmt order=data;
   format group grp.;
   class year;
   table group='' all , year=''*n='';
run;
   

Caveats: Only a few procedures support multilabel formats and I'm not sure Report does. Also Tabulate is somewhat more limited on calculations than Report as you can't calculate with the summaries and "breaks" aren't really available though All does summaries.

Another way would be to repeat the ALL for the group variable with different labels

proc format library=work;
value grpx (multilabel notsorted)
1   = "Group 2"
2   = "Group 3"
;
run;
proc tabulate data=example;
   class group ;
   format group grpx.;
   class year;
   table all='Group 1' group='' all='Total' , year=''*n='';
run;