Hi:
In the interest of providing a PROC REPORT solution, here's what I was able to do with your dummy2 data:
I believe that #3 is the one you want. The reason that the word "Average" was not appearing was that your Location variable is a numeric variable, so in my solution, I needed to make a character version of Location and I called it Char_Loc. Then, at the break in a COMPUTE AFTER block, I could assign the string Average to the Char_loc column. You'll see that in Example #1, before I did the divide I just generated a simple summary and put the string "Grand Total" on the break line. In Example #2, in the COMPUTE AFTER block, I divided the summary values by a hard-coded constant of 10.
Example #3 isn't entirely all PROC REPORT. I did use a PROC SQL step to make the &TOTLOC macro variable that held the count of distinct values for Location, so I could use &TOTLOC in the division instead of a hard-coded value.
Cynthia
Here's the code I used with a copy of DUMMY2 in the WORK library:
** Example 1 Get Grand Total on Break line;
** need character version of Location to hold string;
proc report data=dummy2;
title '1) Make sure Summary Line is correct';
column location char_loc year,(n=yr_tot ) n=Gtot ;
define location / group 'Numeric Loc';
define char_loc / computed 'Char Loc '
style(column)={font_weight=bold};
define year / across;
define yr_tot / "Sum" ;
define Gtot / 'Total';
compute char_loc / character length=20;
char_loc = put(location,words.);
endcomp;
rbreak after / summarize;
compute after;
Char_Loc = 'Grand Total';
endcomp;
run;
** Example 2 Divide by constant value (10) so the values on the break are the average for 10 locations';
proc report data=dummy2;
title '2) Change the style and divide sums on break line by a fixed value';
column location char_loc year,(n=yr_tot ) n=Gtot ;
define location / group /* noprint */;
define char_loc / computed ' '
style(column)={font_weight=bold};
define year / across;
define yr_tot / "Sum" ;
define Gtot / 'Total';
compute char_loc / character length=20;
char_loc = put(location,words.);
endcomp;
rbreak after / summarize;
compute after;
** assuming all you want to do is divide all of the sums by 10;
** use a hard-coded number in the formula;
_c3_ = _c3_ / 10;
_c4_ = _c4_ / 10;
_c5_ = _c5_ / 10;
Gtot = Gtot / 10;
Char_Loc = 'Average';
endcomp;
run;
** Ex 3 Use PROC SQL to get the count of location values;
proc sql noprint;
select count(distinct location) into :totloc
from work.dummy2;
quit;
%put The number of unique location values is &=totloc;
proc report data=dummy2;
title '3) Use a macro variable for the division on the break line';
column location ('Location' char_loc) year,(n=yr_tot ) ('Total' n=Gtot);
define location / group noprint ;
define char_loc / computed ' '
style(column)={font_weight=bold};
define year / across;
define yr_tot / " " ;
define Gtot / ' ';
compute char_loc / character length=20;
char_loc = put(location,words.);
endcomp;
rbreak after / summarize;
compute after;
** Now use the macro variable in the division;
_c3_ = _c3_ / &totloc;
_c4_ = _c4_ / &totloc;
_c5_ = _c5_ / &totloc;
Gtot = Gtot / &totloc;
Char_Loc = 'Average';
endcomp;
run;
... View more
