Can anyone offer a suggestion on how NOT to sum my percentages in last row?(Visual velow code) I need to divide the Total YTD "YOY Difference" by the Total YTD "AEP Sales 2022" rather than summing percentages. OR another option would be to make the make the text in just that one cell the same color as the background to make it look like its not there. Is this possible to do with the compute?
Proc sql; create table AEP_Compare as
select distinct *,
'2022'n-'2021'n as Difference format=comma10.,
('2022'n-'2021'n)/'2021'n as'% Change'n format=percent10.2
From Output
group by 'Submit Date'n,'2021'n, '2022'n
;quit;
proc report data=AEP_Compare 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 'Submit Date'n '2022'n '2021'n Difference '% Change'n ;
define 'Submit Date'n / Display "Application | Submit Date" order=data ;
define '2022'n / Sum "AEP Sales | 2023" style(column)={vjust=c just=c cellwidth = 1in} order=data ;
define '2021'n / Sum "AEP Sales | 2022" style(column)={vjust=c just=c cellwidth = 1in} order=data;
define Difference / "YOY | Difference";
define '% Change'n / "% Change" style(column)={vjust=c just=c cellwidth = 1in} /*format=percent10.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 foreground=%RGB(0,145,204) font_weight=bold borderbottomcolor=%RGB(242,242,242)];
line "AEP Sales YoY Comparison"; /*%sysfunc(intnx(day,%sysfunc(today()),-7),worddate20.);*/
endcomp;
compute 'Submit Date'n;
I + 1;
if mod(i,2) eq 1 then
call define(_row_, "style", "style=[background=%RGB(230,230,230)]");
endcomp;
compute after;
'Submit Date'n = 'Total YTD';
call define (_row_,'style','style=[backgroundcolor=lightgray foreground=black]');
endcomp;
compute '% change'n;
'% change'n=Difference/'2021'n ;
endcomp;
run;
Does your AEP_COMPARE dataset already contain the '% Change'n variable? If so, why would you calculate it again in a COMPUTE block, like you have?
...
compute '% change'n;
'% change'n=Difference/'2021'n ;
endcomp;
...
If you don't actually need to re-compute the '% Change'n column, then the following may work for you.
Try running this example, which creates a report where the column of percentages is not summed in the Total, by making use of the DISPLAY option. Is this along the same lines of what you are hoping to do?
proc sql;
create table have as
select name
,age
,height
,age/height as random_percentage format percent8.2
from sashelp.class;
quit;
proc report data = have split='~'
style(header)={font_weight=bold};
column name age height random_percentage;
define name / 'Name';
define age / sum 'Age';
define height / sum 'Height';
define random_percentage / display 'Random Percentage' format=percent8.2;
compute after;
name = 'Total:';
call define(_row_,'style','style={font_weight=bold}');
endcomp;
rbreak after / summarize dol dul;
run;
Output:
I would use PROC SUMMARY (and perhaps a data step) to get the exact statistics you want on each row. Then once you have the exact SAS data set you want, then use that in PROC REPORT.
Does your AEP_COMPARE dataset already contain the '% Change'n variable? If so, why would you calculate it again in a COMPUTE block, like you have?
...
compute '% change'n;
'% change'n=Difference/'2021'n ;
endcomp;
...
If you don't actually need to re-compute the '% Change'n column, then the following may work for you.
Try running this example, which creates a report where the column of percentages is not summed in the Total, by making use of the DISPLAY option. Is this along the same lines of what you are hoping to do?
proc sql;
create table have as
select name
,age
,height
,age/height as random_percentage format percent8.2
from sashelp.class;
quit;
proc report data = have split='~'
style(header)={font_weight=bold};
column name age height random_percentage;
define name / 'Name';
define age / sum 'Age';
define height / sum 'Height';
define random_percentage / display 'Random Percentage' format=percent8.2;
compute after;
name = 'Total:';
call define(_row_,'style','style={font_weight=bold}');
endcomp;
rbreak after / summarize dol dul;
run;
Output:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.