I have the following proc report where I am taking the subtotal line and subtracting 1 from it, for each group. So for the example report listed below, the first group actually has a subtotal of 2, but I'm having it display 1 instead. This part works fine.
My problem lies with the Grand Total line. I need it to be a summary of all the subtotal rows, but it is summarizing all the data in th Count column instead. For example, the report below it shows 5, but I need it to show 3. I'm not sure how to accomplish this. Any help would be greatly appreciated...
Code:
proc report data = mnr_ct missing nowindows;
columns first_last
maj
mnr
count
;
define first_last / group
style(header)={font=('calibri',10pt,bold) just=c}
style(column)={font=('calibri',10pt) just=c cellwidth=2.0in};
define maj / display
style(header)={font=('calibri',10pt,bold) just=c}
style(column)={font=('calibri',10pt) just=c cellwidth=1.0in};
define mnr / display
style(header)={font=('calibri',10pt,bold) just=c}
style(column)={font=('calibri',10pt) just=c cellwidth=1.0in};
define count / analysis sum
style(header)={font=('calibri',10pt,bold) just=c}
style(column)={font=('calibri',10pt) just=c cellwidth=1.0in};
break after first_last / summarize style=[foreground=black just=c font=('calibri',10pt,bold)];
compute after first_last / style=[background=light grey];
line ' ';
endcomp;
compute count;
if _break_ = 'FIRST_LAST' then
count.sum = count.sum -1;
endcomp;
rbreak after / summarize style=[foreground=black just=c font=('calibri',10pt,bold)];
compute first_last;
if _break_ = 'FIRST_LAST' then
first_last = 'SUBTOTAL';
else if _break_ = '_RBREAK_' then
first_last = 'GRAND TOTAL';
endcomp;
title;
run;
Example Report:
first_last maj min count
something1 aaaaaaa bb 1
aaaaaaa cc 1
subtotal 1
something2 bbbbbbb bb 1
bbbbbbb cc 1
bbbbbbb dd 1
subtotal 2
grand total 5
Solved this by counting the number of obs and subtracting that from the total count.
proc report data = mnr missing nowindows;
columns first_last
maj
mnr
count
obs
gt
;
define first_last / group
style(header)={font=('calibri',10pt,bold) just=c}
style(column)={font=('calibri',10pt) just=c cellwidth=2.0in};
define maj / display
style(header)={font=('calibri',10pt,bold) just=c}
style(column)={font=('calibri',10pt) just=c cellwidth=1.0in};
define mnr / display
style(header)={font=('calibri',10pt,bold) just=c}
style(column)={font=('calibri',10pt) just=c cellwidth=1.0in};
define count / analysis sum
style(header)={font=('calibri',10pt,bold) just=c}
style(column)={font=('calibri',10pt) just=c cellwidth=1.0in};
define obs / computed noprint;
define gt / computed noprint;
break after first_last / summarize style=[foreground=black just=c font=('calibri',10pt,bold)];
compute after first_last / style=[background=light grey];
line ' ';
endcomp;
compute count;
if _break_ = 'FIRST_LAST' then
count.sum = count.sum - 1;
endcomp;
compute first_last;
if _break_ = 'FIRST_LAST' then
first_last = 'SUBTOTAL';
endcomp;
compute obs;
if _break_ = 'FIRST_LAST' then
count+1;
obs=count;
endcomp;
compute gt;
gt = count.sum - obs;
endcomp;
compute after / style=[just=r font=('calibri',10pt,bold)];
line 'GRAND TOTAL' gt;
endcomp;
title;
run;