## How to total some report columns - but not all

Solved
Frequent Contributor
Posts: 88

# How to total some report columns - but not all

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;

Accepted Solutions
Solution
‎07-24-2015 09:45 AM
SAS Super FREQ
Posts: 9,365

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

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

All Replies
Solution
‎07-24-2015 09:45 AM
SAS Super FREQ
Posts: 9,365

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

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

Frequent Contributor
Posts: 88

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

Perfect!!

Thanks Cynthia.

🔒 This topic is solved and locked.