topic Re: How to create a Total YTD row witout totaling percentage? in SAS Programming

How to create a Total YTD row witout totaling percentage?

LMSSAS — Fri, 04 Nov 2022 19:13:04 GMT

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;

Re: How to create a Total YTD row witout totaling percentage?

PaigeMiller — Fri, 04 Nov 2022 20:22:33 GMT

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.

Re: How to create a Total YTD row witout totaling percentage?

mklangley — Fri, 04 Nov 2022 21:19:17 GMT

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:

Re: How to create a Total YTD row witout totaling percentage?

LMSSAS — Tue, 08 Nov 2022 17:59:04 GMT

Thank you for your time and looking over my code. I used your suggestion and it worked perfectly. I appreciate it!!