topic Re: Subtotal and Grand Total in dataset in SAS Programming

Subtotal and Grand Total in dataset

angeliquec — Tue, 15 Jan 2019 08:41:39 GMT

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;

Re: Subtotal and Grand Total in dataset

PeterClemmensen — Tue, 15 Jan 2019 08:51:44 GMT

Are you allowed to use PROC SQL?

Re: Subtotal and Grand Total in dataset

angeliquec — Tue, 15 Jan 2019 08:57:04 GMT

Yes.

Re: Subtotal and Grand Total in dataset

Kurt_Bremser — Tue, 15 Jan 2019 09:13:56 GMT

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;

Re: Subtotal and Grand Total in dataset

Ksharp — Tue, 15 Jan 2019 09:51:38 GMT

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;

Re: Subtotal and Grand Total in dataset

angeliquec — Wed, 16 Jan 2019 01:45:44 GMT

thank you! It's also a good solution