How to total some report columns - but not all

[JerryLeBreton]

How can I create a report that provides a total for 1 column but not another?

For example, I don't want the total of 7 below:

This report should not total the org_count column because, while an organisation only exists in 1 region, it can receive amounts under different awards.  So the total of org_count is double counting some orgs.

So while I need to provide a total for the amount column, how can i suppress the total org_count? 

Here's the code for the above.

TIA

Jerry

-----------------------------------------------------

data have;

    input org  region $  award $  amount org_count;

cards;

10 east A 200 1

10 east B 400 1

20 west B 500 1

30 west A 250 1

30 west B 100 1

40 east A 150 1

40 east B 100 1

;

run;

proc report data=have;

   col region award org_count amount;

   define region / group ;

   define award / group ;

   define org_count / sum;

   define amount / sum ;

   rbreak after / summarize ;

   compute after;

       region='Total';

   endcomp;

run;

[Cynthia_sas · Accepted solution]

Hi:

  You need org_count to be a usage of SUM, so it collapses for your GROUP variables (so that your report collapses from the original 7 rows to 4 rows) with grouping. So, that means you need just a bit of COMPUTE block magic. Essentially, you will let PROC REPORT do its thing and summarize org_count and then in the COMPUTE block for the AFTER, you will add a line of code. This also means you'll need to use the missing= option.

options missing=' ';

... more code ...

proc report ...;

... more ...

   compute after;
       region='Total';
    org_count.sum = .;
   endcomp;

run;

Don't forget to turn missing back to . when you're done with the PROC REPORT step.
    

cynthia

[JerryLeBreton]

Perfect!!

Thanks Cynthia.