SAS Proc Report - Grand Total based off Subtotal?

Question

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

Answer 1

I would store the sum-of-subtotals in a new variable, and display that instead of the automatic summary. That seems the easiest way to get that displayed. I don't know that there is an easy way to get it to happen during the automatic summarization - SAS isn't exactly expecting you to do things the way you did them above (I'm surprised it worked as it did, honestly).

Answer 2

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;