topic Re: add two total rows to summary report in SAS Programming

add two total rows to summary report

Ronein — Wed, 17 Jun 2020 11:16:35 GMT

Hello

What is the best way to add two total lines to summary report dataset (summarytbl)?

Please see the wanted data set in order to show what is the needed data set.

data summarytbl;
input Category $ Y1 Y2 Y3;
cards;
a 10 20 30
b 20 50 40
c 15 30 80
d 50 40 20
;
run;


data wanted;
input Category $ Y1 Y2 Y3;
cards;
a 10 20 30
b 20 50 40
c 15 30 80
Total 45 100 150
d 50 40 20
Total 95 140 170
;
run;

Re: add two total rows to summary report

Ksharp — Wed, 17 Jun 2020 11:27:41 GMT

data summarytbl;
input Category $ Y1 Y2 Y3;
cards;
a 10 20 30
b 20 50 40
c 15 30 80
d 50 40 20
;
run;
data want;
 set summarytbl end=last;
 sum1+y1;
 sum2+y2;
 sum3+y3;
output;
if Category='c' or last then do;
  Category='Total';y1=sum1;y2=sum2;y3=sum3;output;
end;
drop sum:;
run;

Re: add two total rows to summary report

Kurt_Bremser — Wed, 17 Jun 2020 11:31:29 GMT

Which information in the dataset tells you where to put the subtotal?

Re: add two total rows to summary report

Ronein — Wed, 17 Jun 2020 11:37:22 GMT

Hello,
Please see data set called wanted that show the order of the rows:
a
b
c
Total of a b c
d
Total of a b c d

Re: add two total rows to summary report

Kurt_Bremser — Wed, 17 Jun 2020 11:42:49 GMT

@Ronein wrote:
Hello,
Please see data set called wanted that show the order of the rows:
a
b
c
Total of a b c
d
Total of a b c d

This does not answer my question.

Re: add two total rows to summary report

ballardw — Wed, 17 Jun 2020 14:25:36 GMT

If your actual data involves more that those exact 4 rows of data you need to provide the rule(s) that tell us how to determine where a "summary" row goes in the data. Otherwise it is very likely you get a solution that does not extend to your actual data.

If you have a variable that identifies a group of records then tell us that changing that variable is the trigger. If you don't have such a variable then an explicit set of values for your category variable that need to be treated as a group would be another way to know when a summary should occur.

Otherwise shooting in the dark.

If you have a set of values then a multilabel format and Proc Summary or Means could work.

Re: add two total rows to summary report

PaigeMiller — Wed, 17 Jun 2020 14:28:13 GMT

@Ronein wrote:
Hello,
Please see data set called wanted that show the order of the rows:
a
b
c
Total of a b c
d
Total of a b c d

So, if all you want is a program that works on this exact data set, and you never need it to work on any other data set, then @Ksharp has given you the answer, and you should mark his reply as the correct answer.

But, if you want it to work on a different data set, and work in general, you need to provide an explanation of the rules that have to be followed to determine where to place totals.

Re: add two total rows to summary report

Ronein — Fri, 19 Jun 2020 14:22:35 GMT

categories a b c should be order by Y value

Then should have a row of total a+b+c

then row of category d

then row of total a+b+c+d

Re: add two total rows to summary report

Ronein — Fri, 19 Jun 2020 14:24:16 GMT

Is it possible to do it via proc format and then proc summary?

Re: add two total rows to summary report

Kurt_Bremser — Fri, 19 Jun 2020 14:47:22 GMT

@Ronein wrote:

categories a b c should be order by Y value

Then should have a row of total a+b+c

then row of category d

then row of total a+b+c+d

And what identifies the rows a,b,c as belonging to one group, and d to the rest?

Re: add two total rows to summary report

Ronein — Fri, 19 Jun 2020 14:55:42 GMT

It is business decision to look at the summary report like that...

Re: add two total rows to summary report

Kurt_Bremser — Fri, 19 Jun 2020 15:46:47 GMT

@Ronein wrote:
It is business decision to look at the summary report like that...

Then @Ksharp has given you the answer.