add two total rows to summary report

[Ronein]

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;

[Ksharp]

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;

[Kurt_Bremser]

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

[Ronein]

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

[Kurt_Bremser]

---

@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.

[ballardw]

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.

[PaigeMiller]

---

@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.

[Ronein]

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

[Ronein]

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

[Kurt_Bremser]

---

@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?

[Ronein]

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

[Kurt_Bremser]

---

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

---

Then @Ksharp has given you the answer.