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;
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
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
Perfect!!
Thanks Cynthia.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.