topic Re: Need to add a column with row totals for all rows, including computed ones in PROC Report in SAS Programming

Need to add a column with row totals for all rows, including computed ones in PROC Report

RandoDando — Tue, 02 Jul 2024 17:13:48 GMT

I have this code using the across and analysis functions. I want to add 1 column on the far right side which has the row totals going across, including those for the computed Totals. If you are wondering about the single letter coding, I used those to re-word some values and found letters to be easier to manage while assigning the visible text in Proc Format. No numbers because numeric format interfered with the Total labels. If I have to change something, then I know exactly where to go.


proc report data=TEAM_SUMMARY nowd  ;
columns Team Activity Month, cnt ;
define Team/ group order=data;
define Activity/group "Activity" format=$Acty. order=internal;
define Month/across "Month in Play" order=data f=$Month.;
define cnt/analysis "" f=comma18.0;


compute Activity;

if Activity= 'J' then do;
	 call define(_row_, "style", "style=[foreground=Red]");
	 end;
if Activity = 'K' then do;
	 call define(_row_, "style", "style=[foreground=Green]");
	 end;

endcomp;

break after Team/summarize style=[foreground=black just=r font_weight=bold];

rbreak after /summarize style=Header[background=lightgreen just=c];

compute before Team/style=[background=light grey];
line  ' ';
endcomp;

compute after Team;
Activity = 'Team Total';
endcomp;

compute after;
Activity = 'Grand Total';
endcomp;

run;

Re: Need to add a column with row totals for all rows, including computed ones in PROC Report

Cynthia_sas — Tue, 02 Jul 2024 19:06:46 GMT

Hi: Without data, it is hard to tweak your code. However, using an alias, it should be fairly easy to get the row totals on the right side of the report. I'm not sure I understand what you mean when you say: "including those for Computed totals". I don't see any computed totals. Do you mean the CNT values?
Cynthia

Here's an example of using an Alias. Since you did not provide data, I had to make some fake data for a small selection of months, just enough to get the general idea:

This is the code and result:

The final column on the right side was the result of using an ALIAS (Cnt=ColTot). Using an alias essentially allows me to use CNT 2 different ways -- the first way it is UNDER the MONTH ACROSS item and the second way, it is being used without regard to the ACROSS item and it is referred to by the ALIAS name in the DEFINE statement.

Re: Need to add a column with row totals for all rows, including computed ones in PROC Report

RandoDando — Wed, 03 Jul 2024 11:40:29 GMT

That did it, thanks. For the totals I was referring to the original Total and Grand Total rows, which were summed with this.