Hello - I created a table and proc report, code below and image of table. I calculated "AVG APPS RTS" by dividing "Total Contracted / Ready To Sell" at the row level. how do I get the "AVG APPS RTS" at the column level so that in the total bar at the bottom of my table it shows the "AVG APPS RTS" instead as a sum of the rows?
proc sql; create table TOHFinal as
Select Distinct
a.AGENCYNAME,
b.AHIPNeeded format=comma10.,
c.ReadyToTrain format=comma10.,
d.ReadyToSell format=comma10.,
sum(b.AHIPNeeded,c.ReadyToTrain,d.ReadyToSell) as TotalContracted format=comma10.,
0 as '2022 Sales Goal'n,
a.'2022 Sales'n format=comma10.,
calculated TotalContracted/d.ReadyToSell as Avg_Apps_RTS format=comma10.2,
today() as Rundate Format=MMDDYYS10.
From Active_Count2 a
left join Count_AHIPNeeded_TOH b
on a.TOH_AGENCY=b.TOH_AGENCY
left join Count_ReadyToTrain_TOH c
on a.TOH_AGENCY=c.TOH_AGENCY
left join Count_ReadyToSell_TOH d
on a.TOH_AGENCY=d.TOH_AGENCY
Where a.AGENCYNAME is not Null
;
quit;
proc report data=TOHFinal nowd split='|' spanrows
style(header) = [font=("Arial",10.0pt) vjust=middle just=center background=%RGB(0,145,204) foreground=whitesmoke font_weight=bold];
cols AGENCYNAME AHIPNEEDED READYTOTRAIN READYTOSELL TOTALCONTRACTED '2022 SALES GOAL'n '2022 SALES'n AVG_APPS_RTS;
define AGENCYNAME / "AGENCY NAME" order=data ;
define AHIPNEEDED / "AHIP NEEDED" style(column)={vjust=c just=c cellwidth = 1in} order=data;
define READYTOTRAIN / "READY | TO TRAIN" style(column)={vjust=c just=c cellwidth = 1in};
define READYTOSELL / "READY | TO SELL" style(column)={vjust=c just=c cellwidth = 1in};
define TOTALCONTRACTED / "TOTAL CONTRACTED" style(column)={vjust=c just=c cellwidth = 1in} order=data ;
define '2022 SALES GOAL'n / "2022 SALES GOAL" style(column)={vjust=c just=c cellwidth = 1in} order=data;
define '2022 SALES'n / "2022 | SALES" style(column)={vjust=c just=c cellwidth = 1in};
define AVG_APPS_RTS / "AVG | APPS RTS" style(column)={vjust=c just=c cellwidth = 1in};
rbreak after /summarize style=[fontweight=bold];
compute before _page_/style=[font=("Arial",12.5pt) vjust=middle just=center background=whitesmoke foreground=%RGB(0,145,204) font_weight=bold borderbottomcolor=%RGB(242,242,242)];
line "WEEKLY TOH RTS SUMMARY - &worddt";
endcomp;
compute AGENCYNAME;
I + 1;
if mod(i,2) eq 1 then
call define(_row_, "style", "style=[background=%RGB(230,230,230)]");
endcomp;
compute after;
AGENCYNAME = 'Total';
call define (_row_,'style','style=[backgroundcolor=lightgray foreground=black]');
endcomp;
run;
For the columns you want a total average calculated add MEAN. The default is SUM.
define varname / mean ;
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.