topic Re: How to get percentage rather than sum in Total row in SAS Programming

How to get percentage rather than sum in Total row

LMSSAS — Thu, 25 Aug 2022 12:48:54 GMT

hello, i use the below code (proc sql and proc report) to create the calcs in my table. I am trying to solve for having the total % Change not be a sum of the % Change column but be a percentage of the total in the 2 columns. it should be 628/1391=45.14% rather than a total of that column. Can someone advise on how to solve for this?

proc sql; create table FBMI_APPTS5_Output2 as
select distinct 
Agency_Type,
Newly_Appointed as New_Appoint,
RTS_2023 as RTS2023,
RTS2023/New_Appoint as '% Change'n format=percent10.2
from FBMI_APPTS5_Output;
quit;

proc report data=FBMI_APPTS5_Output2 nowd split='|' /* spanrows */
style(header) = [font=("Arial",10.0pt) vjust=middle just=center background=%RGB(0,145,204) foreground=whitesmoke font_weight=bold]
style(column) = [font=("Arial",8.58pt) bordercolor=%RGB(242,242,242) ];

cols Agency_Type New_Appoint RTS2023 '% Change'n;

define Agency_Type / "Agency | Type" order=data ;
define New_Appoint / "Newly Appointed | for PY 2023" style(column)={vjust=c just=c cellwidth = 1in} order=data;
define RTS2023 / "New RTS 2023" order=data ;
define '% Change'n / "% Change" style(column)={vjust=c just=c cellwidth = 1in} ;

rbreak after /summarize  style=[fontweight=bold];

/* Compute block used to create table title */
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 "Comparison of New Agents"; /*%sysfunc(intnx(day,%sysfunc(today()),-7),worddate20.);*/
endcomp;

compute Agency_Type;
	I + 1;
	if mod(i,2) eq 1 then
	call define(_row_, "style", "style=[background=%RGB(230,230,230)]");
endcomp;

compute after;
	Agency_Type = 'Total';
	call define (_row_,'style','style=[backgroundcolor=lightgray foreground=black]');
endcomp;

run;

Re: How to get percentage rather than sum in Total row

Ksharp — Thu, 25 Aug 2022 13:32:09 GMT

Here is an example :


proc report data=sashelp.class nowd split='|' /* spanrows */
style(header) = [font=("Arial",10.0pt) vjust=middle just=center   font_weight=bold]
style(column) = [font=("Arial",8.58pt)  ];

cols name weight height change;

define name / display "Agency | Type" order=data ;
define weight /analysis sum "Newly Appointed | for PY 2023" style(column)={vjust=c just=c cellwidth = 1in} order=data;
define height /analysis sum "New RTS 2023" order=data ;
define change/computed "% Change" style(column)={vjust=c just=c cellwidth = 1in} format=percent8.2;

rbreak after /summarize  style=[fontweight=bold];

/* Compute block used to create table title */
compute before _page_/style=[font=("Arial",12.5pt) vjust=middle just=center background=whitesmoke  font_weight=bold ];
line "Comparison of New Agents"; /*%sysfunc(intnx(day,%sysfunc(today()),-7),worddate20.);*/
endcomp;

compute name;
	I + 1;
	if mod(i,2) eq 1 then
	call define(_row_, "style", "style=[background=greyee");
endcomp;

compute after;
	name = 'Total';
	call define (_row_,'style','style=[backgroundcolor=lightgray foreground=black]');
endcomp;

compute change;
change=height.sum/weight.sum;
endcomp;
run;

Re: How to get percentage rather than sum in Total row

Sajid01 — Thu, 25 Aug 2022 13:47:57 GMT

Hello
Add the given code after your first Proc SQL. That should serve the purpose.
If needed modify the proc report.

proc sql;
update test set '% Change'n=100*(RTS_2023/New_Appoint)
where Agency_type ="Total";
quit;

Re: How to get percentage rather than sum in Total row

LMSSAS — Thu, 25 Aug 2022 19:55:27 GMT

Thank you!

Re: How to get percentage rather than sum in Total row

LMSSAS — Thu, 25 Aug 2022 19:55:42 GMT

Thank you!