topic Proc Report-Grand/Sub Total in SAS Programming

Proc Report-Grand/Sub Total

sasuser_sk — Tue, 15 Dec 2020 20:28:14 GMT

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

Re: Proc Report-Grand/Sub Total

ChrisNZ — Wed, 16 Dec 2020 04:00:11 GMT

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

Re: Proc Report-Grand/Sub Total

RichardDeVen — Wed, 16 Dec 2020 05:44:01 GMT

You can use a combination of two tricks

Special missing values formatted to display as 'Sub-Total'
CALL DEFINE to change the PRETEXT to 'Grand Total' in column 1

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