Help using Base SAS procedures

How to total some report columns - but not all

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 85
Accepted Solution

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: 8,685

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

View solution in original post


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

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: 85

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

Perfect!!

Thanks Cynthia.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 230 views
  • 0 likes
  • 2 in conversation