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;
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 .
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 .
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.
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;
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;
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
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.