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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.