How to get subtotal/grand total right

odmhx — Fri, 31 Jul 2009 14:51:01 GMT

I am trying to get subtotals based on ffp_type, but just couldn't get it right.

Here is my code:

%macro createfile(board_num); /*to create board files repeatedly according to the board number*/
%do i = 1 %to &board_num;
data bd&&bdnum&i;
retain reg arr bcp 0;
set final3;
if w = "&&bdnum&i";
run;

options pageno = 1 nodate linesize = 80;

proc report data = bd&&bdnum&i nowd split = '*' missing;
column company fy ffp_type ffp hsreimb hsreimb=compreg hsreimb=comparr hsreimb=compbcp;

define company /group 'Company' format = $char10.;
define fy /group 'FY';
define ffp_type /group 'FFP Type' order=internal format = $ffptype.;
define ffp /group 'FFP %';
define hsreimb /analysis 'Total*Reimb.' sum format = comma10.2 ;
define compreg /sum noprint;
define comparr /sum noprint;
define compbcp /sum noprint;

compute before company;
comptot = 0; compreg =0; comparr = 0; compbcp =0;
endcomp;

compute before fy;
regsum = 0; arrsum = 0; bcpsum = 0; fytot = hsreimb.sum;
endcomp;

compute after ffp_type;
if ffp_type = '1' then do;
regsum = hsreimb.sum; compreg = +hsreimb.sum; end;
else if ffp_type = '2' then do;
arrsum = hsreimb.sum; comparr = +hsreimb.sum; end;
else do;
bcpsum = hsreimb.sum; compbcp = +hsreimb.sum; end;

endcomp;

compute after fy;
line ' ';
line 'Subtotal for Regular' @54 regsum comma10.2;
line 'Subtotal for ARRA' @54 arrsum comma10.2;
line 'Subtotal for BCCP_CHIP' @54 bcpsum comma10.2;
line 'SFY Total ' @54 fytot comma10.2;
line ' ';

endcomp;

compute after company;
comptot = hsreimb.sum;
line 'Company Reg Total' @54 compreg comma10.2;
line 'Company Arr Total' @54 comparr comma10.2;
line 'Company Total' @54 comptot comma10.2;
endcomp;

title1 'Program ID: mcd_manual_reversal page: ' ;

%end;
%mend createfile;

%createfile(&board_cnt);

Here is my result (partial)

Total
Company FY FFP Type FFP % Reimb.
XXXX 2009 REGULAR 62.14 1,539.15
ARRA 71.29 543.98

Subtotal for Regular 1,539.15
Subtotal for ARRA 543.98
Subtotal for BCCP_CHIP 0.00
SFY Total 2,083.13

Company Reg Total 2,083.13
Company Arr Total 2,083.13
Company Total 2,083.13
Program ID: mcd_manual_reversal page: 1

Total
Company FY FFP Type FFP % Reimb.
YYYYB 2009 REGULAR 62.14 34,558.03
ARRA 71.29 10,320.02
BCCP_CHIP 73.5 5,623.03

Subtotal for Regular 34,558.03
Subtotal for ARRA 10,320.02
Subtotal for BCCP_CHIP 5,623.03
SFY Total 50,501.08

Company Reg Total 50,501.08
Company Arr Total 50,501.08
Company Total 50,501.08

Can anybody debug my program? I really appreciate it!

Re: How to get subtotal/grand total right

odmhx — Fri, 31 Jul 2009 15:02:49 GMT

The output in my previous post doesn't align right. Here is another try:

Program ID: mcd_manual_reversal page: 1

Total
Company FY FFP Type FFP % Reimb.
XXXXX 2009 REGULAR 62.14 1,539.15
ARRA 71.29 543.98

Subtotal for Regular 1,539.15
Subtotal for ARRA 543.98
Subtotal for BCCP_CHIP 0.00
SFY Total 2,083.13

Company Reg Total 2,083.13
Company Arr Total 2,083.13
Company Total 2,083.13

Program ID: mcd_manual_reversal page: 1

Total
Company FY FFP Type FFP % Reimb.
YYYYY 2009 REGULAR 62.14 34,558.03
ARRA 71.29 10,320.02
BCCP_CHIP 73.5 5,623.03

Subtotal for Regular 34,558.03
Subtotal for ARRA 10,320.02
Subtotal for BCCP_CHIP 5,623.03
SFY Total 50,501.08

Company Reg Total 50,501.08
Company Arr Total 50,501.08
Company Total 50,501.08

Re: How to get subtotal/grand total right

ChrisNZ — Tue, 04 Aug 2009 23:12:31 GMT

You need to do some of the work.
Please boil the problem down a bit: provide less code if possible, some data (or use sashelp tables), and explain what you think is wrong.

topic Re: How to get subtotal/grand total right in SAS Procedures

How to get subtotal/grand total right

Re: How to get subtotal/grand total right

Re: How to get subtotal/grand total right