Subtotal and Grand Total in dataset

[angeliquec]

Hi,

I have this dataset where I want to display the subtotals and grand total without using a proc tabulate.

From my "HAVE" dataset, I'd like the totals (num1 and num2) be calculated for each Group, and the grand total be calculated, placed in the first cell of the "WANT" dataset. I'd like to have the output in SAS dataset form, not Proc Report nor Tabulate. Thank you!

data have;
input Group :$20. Member :$20. num1 num2;
datalines;
Group_a Member_a_1 6 0
Group_a Member_a_2 4 1
Group_b Member_b_1 6 3
Group_b Member_b_2 3 1
;
run;

data want;
input final_text :$20. num1 num2;
datalines;
Grand_Total 19 5
Group_a 10 1
Member_a_1 6 0
Member_a_2 4 1
Group_b 9 4
Member_b_1 6 3
Member_b_2 3 1
;
run;

[PeterClemmensen]

Are you allowed to use PROC SQL?

[angeliquec]

Yes.

[Kurt_Bremser]

Data step language is good for this:

data have;
input Group :$20. Member :$20. num1 num2;
datalines;
Group_a Member_a_1 6 0
Group_a Member_a_2 4 1
Group_b Member_b_1 6 3
Group_b Member_b_2 3 1
;
run;

data want;
set have end=eof;
by group;
retain
  sum_num1
  sum_num2
  grand_num1 0
  grand_num2 0
;
if first.group
then do;
  sum_num1 = 0;
  sum_num2 = 0;
end;
grand_num1 + num1;
grand_num2 + num2;
sum_num1 + num1;
sum_num2 + num2;
output;
if last.group
then do;
  member = 'Group Sum';
  num1 = sum_num1;
  num2 = sum_num2;
  output;
end;
if eof
then do;
  member = 'Grand Total';
  num1 = grand_num1;
  num2 = grand_num2;
  output;
end;
drop sum: grand:;
run;

[angeliquec]

thank you! It's also a good solution

[Ksharp · Accepted solution]

Open want dataset.

data have;
input Group :$20. Member :$20. num1 num2;
datalines;
Group_a Member_a_1 6 0
Group_a Member_a_2 4 1
Group_b Member_b_1 6 3
Group_b Member_b_2 3 1
;
run;

ods select none;
proc report data=have out=want nowd;
column  Group  Member  num1 num2;
define group/group;
compute before;
 Member='Grand_Total';
endcomp;
compute before group;
 Member=group;
endcomp;
break before group/summarize;
rbreak before/summarize;
run;
ods select all;