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 ;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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