BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
LMSSAS
Quartz | Level 8

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?

LMSSAS_0-1661431237363.png

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

Ksharp_0-1661434323270.png

 

View solution in original post

4 REPLIES 4
Ksharp
Super User

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;

Ksharp_0-1661434323270.png

 

Sajid01
Meteorite | Level 14

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 4 replies
  • 795 views
  • 1 like
  • 3 in conversation