BookmarkSubscribeRSS Feed
LMSSAS
Quartz | Level 8

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;

Lisa_Sessions_2-1651840564857.png

 

 

1 REPLY 1
JOL
SAS Employee JOL
SAS Employee

For the columns you want a total average calculated add MEAN.  The default is SUM.

define varname / mean ;

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 800 views
  • 0 likes
  • 2 in conversation