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;
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;
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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.