I'm trying to Print grand total and subtotal. The code is doing its job but labeling of grand total and subtotal is not showing up. Help will be appreciated. Thanks! My code is:
proc report data=Test ;
column Month Customer_ID Bill Code Contractor Bill_1 Bill_2 TOT_AMT;
define Month/group;
define Customer_ID / group;
define Bill Code /group;
define Contractor /group;
/* define statistics*/
define Bill_1/sum format=comma12.2;
define Bill_2/sum format=comma12.2;
define TOT_AMT /sum format=comma12.2;
compute after Month;
Month ='Sub-Total'; endcomp; rbreak after/summarize;
compute after ;
Month='Grand Total'; /*Column or grand total*/
endcomp; break after Month /summarize;
run;
The result is:
Month | Customer_ID | Bill Code | Contractor | Bill_1 | Bill_2 | TOT_AMT |
202001 | 2908 | S10 | NET | 0 | -2,064.69 | -2,064.69 |
S13 | REN | 6,882.30 | 0 | 6,882.30 | ||
S16 | HIS | 1,134.30 | 0 | 1,134.30 | ||
6,882.30 | 0 | 5,951.91 | ||||
202002 | 2908 | S13 | REN | 6,882.30 | 0 | 6,882.30 |
S16 | HIS | 1,134.30 | 0 | 1,134.30 | ||
6,882.30 | 0 | 8,016.60 | ||||
202003 | 2908 | S13 | REN | 6,882.30 | 0 | 6,882.30 |
S16 | HIS | 1,134.30 | 0 | 1,134.30 | ||
6,882.30 | 0 | 8,016.60 | ||||
24,049.80 | -2,064.69 | 21,985.11 |
No data to test. Maybe this helps:
proc report data=SASHELP.CLASS;
columns AGE AGE_CHAR SEX WEIGHT;
define AGE /group noprint;
define AGE_CHAR/computed format=$8. 'Age' ;
define SEX /group ;
define WEIGHT /sum;
compute AGE_CHAR/char;
AGE_CHAR=put(AGE,8.);
endcomp;
break after AGE /summarize;
compute after AGE;
AGE_CHAR='Subtotal';
endcomp;
run;
Age | Sex | Weight |
---|---|---|
11 | F | 50.5 |
. | M | 85 |
Subtotal | 135.5 | |
12 | F | 161.5 |
. | M | 310.5 |
Subtotal | 472 | |
13 | F | 182 |
. | M | 84 |
Subtotal | 266 | |
14 | F | 192.5 |
. | M | 215 |
Subtotal | 407.5 | |
15 | F | 224.5 |
. | M | 245 |
Subtotal | 469.5 | |
16 | M | 150 |
Subtotal | 150 |
You can use a combination of two tricks
Example:
data have;
call streaminit(2020);
do customer_id = 2908;
do date = '01jan2020'd to '31mar2020'd;
billcode = 'S' || cats(rand('integer',11,12));
contractor = scan("NET REN REN", rand('integer',3));
bill1 = rand('integer', -1000, 7000);
bill2 = rand('integer', -1000, 7000);
total = sum(bill1,bill2);
output;
end;
end;
run;
proc format ;
picture monyyd (default=14)
low - high = '%b-%Y' (datatype=date)
;
value totlabel
.S = 'Sub-Total'
other = [monyyd.]
;
ods html file='report.html';
proc report data=have;
column date customer_id billcode contractor bill1 bill2 total;
define date / 'month' group order=internal format=totlabel.;
define customer_id / group;
define billcode / group;
define contractor / group;
define bill1 / sum format=comma12.;
define bill2 / sum format=comma12.;
define total / sum format=comma12.;
break after date / summarize;
compute after date;
date = .S;
call define (_row_, 'style', 'style=[fontweight=bold background=cxe0e0e0]');
endcomp;
rbreak after / summarize style=[background=cxf0f0f0 fontweight=bold fontsize=12pt];
compute after;
call define ('_c1_', 'style', 'style=[pretext="Grand Total"]');
endcomp;
run;
ods html close;
Produces
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.