sasproc-report

SAS Proc Report - Grand Total based off Subtotal?


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

Solution

  • 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;