Calculate grand total in subtotal row

[HGimenez]

Hi! 

I want to show the grand total value in a subtotal row. Take for instance SASHELP.CLASS dataset. The total sum of weights of all the students is 1900.5. The sum of weights for male students is 1089.5, and for females is 811

I want to create a column that, for each student, shows the weight. But, for the Sex subtotals, shows 1900.5 both for M and F (instead of 1089.5 and 811)

I found a way of achieving it, but is a little awkward. I create a dummy column as weight divided by PCTSUM to get the total. Then I create a new column that shows either the value of weight or the dummy column. 

I am sure there is a more straightforward way of achieving it. Any ideas? I tried using SUM instead of PCTSUM, but it doesn't calculate the grandtotal. Is there something like a TOTSUM function?

This is the code, the column that achieves the desired outcome is weightCustomSubtotal

PROC REPORT DATA=SASHELP.CLASS; 
	COLS SEX NAME AGE WEIGHT weight=dummy_pctsum totweight weightCustomSubtotal;
	DEFINE SEX / GROUP;
	DEFINE weightCustomSubotal / COMPUTED;

	define dummy_pctsum / analysis PCTSUM 'dummy pctsum'; 
	define dummy_totweight / COMPUTED;

	compute totweight;
		totweight = weight.sum / dummy_pctsum; 
		endcomp;
	BREAK AFTER SEX / SUMMARIZE;

	COMPUTE weightCustomSubtotal;
		IF _BREAK_ = "" THEN
			weightCustomSubtotal = weight.sum;
		ELSE 
			weightCustomSubtotal = totweight;
		ENDCOMP;
	RUN;

Thanks a lot!!

[Cynthia_sas · Accepted solution]

Hi:

  It's not clear what you want to do. Your code generates these warnings that point to some of the issues.

WARNING: dummy_totweight is not in the report definition.

WARNING: weightCustomSubotal is not in the report definition.

NOTE: Groups are not created because the usage of Name is DISPLAY. To avoid this note, change all GROUP variables to ORDER variables.

The note tells you one of the fixes -- you have to change the usage of SEX to ORDER (not group) so you can do break processing. 

I think you might be trying to calculate the percent of the Grand Total (which you will get from PCTSUM) and the percent of the sub-group total for each value of SEX. I can't take screen shots right now, but try this code and see if it gets you closer.

Cynthia

options missing=' ';
   PROC REPORT DATA=SASHELP.CLASS out=repout; 
    column SEX NAME AGE WEIGHT weight=wpct  subtot subpct;
    DEFINE SEX / order;
    define weight / analysis sum;
    define wpct / analysis PCTSUM 'pctsum of grand tot' f=percent9.2; 
    define subtot / computed 'subtotal for group';
    define subpct / computed 'pct of subgrp' f=percent9.2;
    compute before sex;
      holdsub = weight.sum;
    endcomp;
    compute subpct;  
      subtot = holdsub;
      subpct = weight.sum /holdsub;
      if upcase(_break_) = '_RBREAK_'  
          then subpct = .;
    endcomp;
    compute after sex;
      name = 'SubTot';
     endcomp;
     compute after;
       name = 'GrandTot';
       subtot = .;
     endcomp;
     BREAK AFTER SEX / SUMMARIZE;
     rbreak after / summarize;

    RUN;

[HGimenez]

Cynthia,

Thanks a lot for your reply! Your solution does work, the key in your code that I was missing is to create a hold variable in the compute before

Here's a simplified version of your code that calculates only the column that I need

options missing=' ';
   PROC REPORT DATA=SASHELP.CLASS out=repout; 
    column sex name weight weightCustomSubtotal;
	define sex / order;
    define weight / noprint;
    compute before;
    	holdtotal = weight.sum;
	    endcomp;
    compute weightCustomSubtotal; 
		if _BREAK_ = 'Sex' THEN
	    	weightCustomSubtotal = holdtotal;
		else 
			weightCustomSubtotal = weight.sum;
 		endcomp;
    BREAK AFTER SEX / SUMMARIZE;
    RUN;

By the way, if I change sex from ORDER to GROUP it also works with no warnings. At least in SAS 9.4. See the code below

options missing=' ';
   PROC REPORT DATA=SASHELP.CLASS out=repout; 
    column sex name weight weightCustomSubtotal;
	define sex / group;
    define weight / noprint;
    compute before;
    	holdtotal = weight.sum;
	    endcomp;
    compute weightCustomSubtotal; 
		if _BREAK_ = 'Sex' THEN
	    	weightCustomSubtotal = holdtotal;
		else 
			weightCustomSubtotal = weight.sum;
 		endcomp;
    BREAK AFTER SEX / SUMMARIZE;
    RUN;

[Cynthia_sas]

Hi:

  When I run in 9.4M6, I still see a NOTE with the SAME message. You got rid of the WARNINGS by fixing the COLUMN statement. Here's my log:

  Do you see that NOTE? This is still something that you should consider fixing. When you have NAME in the report definition, you are NOT really "grouping" or collapsing rows. You are ORDERing the rows based on the value of the ORDER variable. And, yes, that treats each value of SEX as an ORDERed set --- for the purpose of BREAK processing. But in your usage scenario, the correct usage is ORDER.

Cynthia

[HGimenez]

Cynthia, thanks again for your reply

No - I don't get that note

I am using

SAS Enterprise Guide version: 7.15 HF7 (7.100.5.6177)
SAS System version: 9406.100.23523.40334