topic Re: How to total some report columns - but not all in SAS Procedures

How to total some report columns - but not all

JerryLeBreton — Fri, 24 Jul 2015 12:22:42 GMT

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;

Re: How to total some report columns - but not all

Cynthia_sas — Fri, 24 Jul 2015 13:45:16 GMT

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

Re: How to total some report columns - but not all

JerryLeBreton — Fri, 24 Jul 2015 22:45:24 GMT

Perfect!!

Thanks Cynthia.