Subtotals and Grand Total in dataset with transpose

[angeliquec]

Hi, I'd like to request assistance again in getting the subtotals and totals from this dataset, also transposing the variable "class" from long to wide. Let's say I would not have any idea how many distinct values of "class" will there be.

My preferred result would be a SAS dataset, in "WANT2".

Thank you!

data have2;
input Group_Name :$20. Member :$20. class $4. num;
datalines;
Group_a Member_a_1 num1 6
Group_a Member_a_2 num1 4
Group_b Member_b_1 num1 6
Group_b Member_b_2 num1 3
Group_a Member_a_1 num2 0
Group_a Member_a_2 num2 1
Group_b Member_b_2 num2 1
;
run;

data want2;
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 9 3
Member_b_2 0 1
;
run;

[Reeza]

Do you want a data set or a displayed report?
Otherwise use PROC MEANS with a CLASS statement to get the sun totals and totals. Put the grouping variables in the CLASS statement and variables you want summarized in the VAR statement.

[angeliquec]

Hi Reeza. I'd like to have a dataset. Thank you!

[r_behata]

Did not get your requirements completely, see if this helps:

data have2;
input Group_Name :$20. Member :$20. class $4. num;
datalines;
Group_a Member_a_1 num1 6
Group_a Member_a_2 num1 4
Group_b Member_b_1 num1 6
Group_b Member_b_2 num1 3
Group_a Member_a_1 num2 0
Group_a Member_a_2 num2 1
Group_b Member_b_2 num2 1
;
run;

proc means data=have2 nway noprint n ;
class Group_Name Member;
var num;
ways 0 1 2;
output out=want(drop=_type_ _freq_ _way_)  sum=/ ways;
run;

[Ksharp · Accepted solution]

data have2;
input Group_Name :$20. Member :$20. class $4. num;
datalines;
Group_a Member_a_1 num1 6
Group_a Member_a_2 num1 4
Group_b Member_b_1 num1 6
Group_b Member_b_2 num1 3
Group_a Member_a_1 num2 0
Group_a Member_a_2 num2 1
Group_b Member_b_2 num2 1
;
run;
proc summary data=have2 nway;
class  Group_Name  Member class;
var num;
output out=temp(drop=_:) sum=;
run;
proc transpose data=temp out=temp1(drop=_:);
by Group_Name  Member;
id class;
var num;
run;


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