PROC Report Summary Row for columns based on row criteria

[rapunzelle13]

I have been successful at adding a summary row below each category that sums all the columns. I am trying to insert a second summary row, but this one is the the total of all rows above that meet a specific criteria. Each row in orange in the sample needs to be summed up for the second totals row, also in orange. 

I am a beginner with PROC REPORT, so please forgive my ignorance at this point. If this just isn't possible, I can use an excel macro after the export, but would love to not have to do that. 

My PROC REPORT code is currently as follows: 

PROC REPORT DATA=WORK.COUNT_RPT NOWD SPLIT='*' /*BOX  */
STYLE(HEADER)=[BACKGROUND=WHITE COLOR=BLACK FONTFAMILY='Arial Unicode MS' FONTSIZE=8PT]
STYLE(COLUMN)=[BACKGROUND=WHITE COLOR=BLACK FONTFAMILY='Arial Unicode MS' FONTSIZE=8PT ]
STYLE(LINES)={BACKGROUND=WHITE COLOR=BLACK FONTFAMILY='Arial Unicode MS' FONTSIZE=8PT}
;

WHERE ACCUM_TYPE_DESCR IN('INDIVIDUAL T1 DED','FAMILY T1 DED','INDIVIDUAL INN DED','FAMILY INN DED','INDIVIDUAL OON DED','FAMILY OON DED');
COLUMN 
	POLICY7  
	PLAN 
	ACCUM_TYPE_DESCR  
	PLAN_TYPE
	RPT_TIER
	ACCUM_LIMIT1
	LIMIT_MET
	COUNT_DISTINCT
	SUM_of_ACCUM_YTD1
	AVG_of_ACCUM_YTD1
	PCT_TOTAL;
DEFINE  POLICY7 /  DISPLAY 'Policy' F=$12. 
STYLE(COLUMN)={JUST=L};
DEFINE 	PLAN / DISPLAY 'Plan' 
STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100%};
DEFINE ACCUM_TYPE_DESCR / ORDER=DATA GROUP 'Accumulator Description';
	BREAK AFTER ACCUM_TYPE_DESCR /SUMMARIZE;
DEFINE	PLAN_TYPE / DISPLAY  'PLAN Type'
STYLE(COLUMN)={JUST=L};
DEFINE	RPT_TIER / DISPLAY 'Report Tier' 
STYLE(COLUMN)={JUST=L};
DEFINE	ACCUM_LIMIT1 / DISPLAY 'Plan Limit'  
STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:$#,##0_);[RED]\($#,##0\)'};
DEFINE	LIMIT_MET / DISPLAY 'Limit Met Category' 
STYLE(COLUMN)={JUST=L};
DEFINE	COUNT_DISTINCT / ANALYSIS SUM 'Count'  
STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:#,##0_)'};
DEFINE	SUM_of_ACCUM_YTD1 / ANALYSIS SUM 'Total YTD*Accumulations'  
STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:$#,##0_);[RED]\($#,##0\)'};
DEFINE	AVG_of_ACCUM_YTD1 / ANALYSIS SUM 'Average*Accumulation'  
STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:$#,##0_);[RED]\($#,##0\)'};
DEFINE	PCT_TOTAL / ANALYSIS SUM '% of Total'  
STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:#,##0.00%_)'};
RBREAK AFTER / SKIP;
/*COMPUTE AFTER  ACCUM_TYPE_DESCR ;
LINE ' ';*/
COMPUTE AFTER  ACCUM_TYPE_DESCR ;
LINE ' ';
ACCUM_TYPE_DESCR= CATT(ACCUM_TYPE_DESCR,' TOTALS');
DO i=1 TO 7;
/*CALL DEFINE (i,'STYLE','STYLE=[FONT_WEIGHT=BOLD bordertopwidth=4PT borderbottomwidth=4PT bordercolor=black
		borderstyle=solid BORDERRIGHTCOLOR=WHITE BORDERLEFTCOLOR=WHITE]');*/
CALL DEFINE (i,'STYLE','STYLE=[FONT_WEIGHT=BOLD');
END;
DO i=8 TO 11;
/*CALL DEFINE (i,'STYLE','STYLE=[FONT_WEIGHT=BOLD
		bordertopwidth=4PT borderbottomwidth=4PT bordercolor=black
		borderstyle=solid]');*/
		CALL DEFINE (i,'STYLE','STYLE=[FONT_WEIGHT=BOLD');
		END;
ENDCOMP;

COMPUTE PCT_TOTAL ;
IF LIMIT_MET = '4.YES-LIMIT MET' THEN DO;
    DO i = 7 to 11; 
    CALL DEFINE(i,'STYLE','STYLE=[BACKGROUND=#ffda03]');
	END;
END;
/*for the same criteria LIMIT_MET='4.YES-LIMIT MET' INSERT THE COUNT TOTAL, YTD ACCUM TOTAL, AND AVG YTD ACCUM UNDER THE OVERALL TOTALS*/
ENDCOMP;

RUN;

[Ksharp · Accepted solution]

data have;
 set sashelp.heart(obs=100);
run;
proc sql;
create table want as
select status length=200,BP_Status,sum(ageatstart) as age,sum(weight) as weight,sum(height) as height
 from  have
  group by status,BP_Status
union all
select 'Overall Total',' ',sum(ageatstart),sum(weight),sum(height) from have
union all
select 'Total For Only Those Optimal',' ',
(select sum(ageatstart) from have where BP_Status='Optimal'),
(select sum(weight) from have where BP_Status='Optimal'),
(select sum(height) from have where BP_Status='Optimal')
from have(obs=1)
;
quit;

ods excel file='c:\temp\temp.xlsx';
proc report data=want nowd;
columns status BP_Status age weight height;
define _all_/display;
compute height;
if BP_Status='Optimal' then do;
call define('age','style','style={background=yellow}');
call define('weight','style','style={background=yellow}');
call define('height','style','style={background=yellow}');
end;
if status='Total For Only Those Optimal' then call define(_row_,'style','style={background=yellow}');
endcomp;
run;
ods excel close;

You could pre-process data before PROC REPORT .

[rapunzelle13]

Would this work the same if you had totals by status (Alive /Dead)? Would it insert the Optimal total under each status category? 

I actually have up to 6 different groups on one report. Here's just 2 of them. The other 4 could possibly be Individual INN Ded, Family INN Ded, Individual OON Ded, Family OON Ded. Each overall total is only for the specific group, and then the Limit Met totals are for the highlighted rows. 

[rapunzelle13]

I've added a data set file (all dummy data, so no privacy issues) if that makes it easier to work with.

[Ksharp]

"Would this work the same if you had totals by status (Alive /Dead)? Would it insert the Optimal total under each status category? "
Sure. Could do it .All you need to do is pre-process dataset via SQL before PROC REPORT.

"I actually have up to 6 different groups on one report."
It is not a big deal. Just include all these six variables in GROUP BY of SQL and COLUMNS of PROC REPORT .

[BrunoMueller]

Here is an example using sashelp.class. The report has a _dummy column to do all the computations.

So for each report line, we check the contents of a value, if true we do use the SUM statement (var + expression). i this way the value is retained and can then later be used in the overall total line. Currently the content of the _dummy column is printed as well, just uncomment the noprint and it will not be displayed. I extended the length of column sex to have space for the custom total line.

data newclass;
  length sex $ 64;
  set sashelp.class;
run;

proc report data=newclass;
  column sex name age  _dummy;
  define sex / order;
  define name / order;
  define age / analysis sum;
  define _dummy / computed /* noprint */;

  compute _dummy / char length=32;
    
    length add13 8;
    if age.sum = 13 then do;
      call define("age.sum", "style", "style={background=bioy}");
      add13 + age.sum;
    end;
    
      _dummy = catx(":", _break_, add13);

    if _break_ = "_RBREAK_" then do;
       age.sum = add13;
       sex = "total for 13's";
    end;
    
  endcomp;

  break after sex / summarize;

  rbreak after / summarize;
run;

[rapunzelle13]

I'm not understanding how to translate this to my code because you're using the Age as the sum analysis and also the criteria.
My report needs to use the Limit_met category value as the criteria and summing the Count, YTD accumulation, YTD average, and Percent of Total columns if the Limit_met criteria is '4.Yes-Limit Met'

[BrunoMueller]

Find below a new program. This time it checks for a "n" in the name, and uses this condition to sum up the special total values.

You also need an additional grouping variable to create an additional "break" line. the code also sums up age and height.

data newclass;
  length sex $ 64;
  set sashelp.class;

  * needed for special summary line;
  sex2 = sex;
run;

proc report data=newclass;
  column sex2 sex name age height  _dummy;
  define sex2 / order /* noprint */;
  define sex / order;
  define name / order;
  define age / analysis sum;
  define height / analysis sum format=comma10.1;

  define _dummy / computed /* noprint */;

  compute _dummy / char length=32;

    * declare variable to keep special total;
    length ageTotal2 8 heightTotal2 8;

    if find(name, "n") > 0 then do;
      call define("name", "style", "style={background=bioy}");
      call define("age.sum", "style", "style={background=bioy}");
      call define("height.sum", "style", "style={background=bioy}");

      * use of SUM statement, so values are retained;
      ageTotal2 + age.sum;
      heightTotal2 + height.sum;
    end;

    * for illustration only to see whats going on;
    _dummy = catx(":", _break_, ageTotal2, heightTotal2);

    * detect special total line and fill columns as needed;
    if upcase(_break_) = "SEX2" then do;
      age.sum = ageTotal2;
      height.sum = heightTotal2;
      sex = "total for name ? n";

      * reset ageTotal2 as we begin a new group;
      call missing(ageTotal2, heightTotal2);
    end;
  endcomp;

  break after sex / summarize;
  break after sex2 / summarize;

  * rbreak after / summarize;
run;

[rapunzelle13]

I ended up using this solution rather than the one marked as accepted. Eventually after messing around with the sorting, order and group, I was able to come up with exactly what I needed. Thanks so much for the help! 

If I could ask one last question: Is it possible to make it so the column headers repeat with each different group? If I need to submit it as a separate question, I'm happy to do so. @BrunoMueller 

[BrunoMueller]

Glad it is working for you. I would enter a new question, also mention the desired output format (html, pdf, rtf, ...) in the question.