Hi,
i have the following data set ,
I need sum of group1 group2 and group3 in 4 th observation with Value as 'Tot' for Var1 Variable .
obs Var Var1 grp1 grp2 grp3
1) best A 3 4 6
2) best B 4 1 7
3) best C 1 3 1
4) Worst G 5 7 1
5) Worst E 5 2 6
Expected :output;
Var Var1 grp1 grp2 grp3
1) best A 3 4 6
2) best B 4 1 7
3) best C 1 3 1
4) Tot 8 8 14
5) Worst G 5 7 1
6) Worst E 5 2 6
7) Tot 10 9 7 (sum of "Worst")
Thanks.
Please try
data have;
input Var$ Var1$ grp1 grp2 grp3;
cards;
best A 3 4 6
best B 4 1 7
best C 1 3 1
Worst G 5 7 1
Worst E 5 2 6
;
proc sql;
create table want as select a.* from have as a
union corr
select var, 'Tot' as var1, sum(grp1) as grp1, sum(grp2) as grp2, sum(grp3) as grp3 from have where var='best'
union corr
select var, 'Tot' as var1, sum(grp1) as grp1, sum(grp2) as grp2, sum(grp3) as grp3 from have where var='Worst' group by var;
quit;
Please try
data have;
input Var$ Var1$ grp1 grp2 grp3;
cards;
best A 3 4 6
best B 4 1 7
best C 1 3 1
Worst G 5 7 1
Worst E 5 2 6
;
proc sql;
create table want as select a.* from have as a
union corr
select var, 'Tot' as var1, sum(grp1) as grp1, sum(grp2) as grp2, sum(grp3) as grp3 from have where var='best'
union corr
select var, 'Tot' as var1, sum(grp1) as grp1, sum(grp2) as grp2, sum(grp3) as grp3 from have where var='Worst' group by var;
quit;
Alternatively by data step
data have;
input Var$ Var1$ grp1 grp2 grp3;
cards;
best A 3 4 6
best B 4 1 7
best C 1 3 1
Worst G 5 7 1
Worst E 5 2 6
;
data want;
set have;
retain _grp1;
by var notsorted;
output;
if first.var then do; _grp1=.; _grp2=.; _grp3=.;end;
if grp1 ne . then _grp1+grp1;
if grp2 ne . then _grp2+grp2;
if grp3 ne . then _grp3+grp3;
grp1=_grp1;
grp2=_grp2;
grp3=_grp3;
var1='Tot';
if last.var;
drop _:;
output;
run;
Don't clutter up your dataset with additional observations that will only cause confusion later on.
The SAS reporting procedures (especially PROC REPORT) allow the dynamic creation of summary lines in the printed output.
That said, try a data step:
data have;
input var $ var1 $ grp1 grp2 grp3;
datalines;
best A 3 4 6
best B 4 1 7
best C 1 3 1
Worst G 5 7 1
Worst E 5 2 6
;
data want;
set have;
by var notsorted;
array invars {3} grp1-grp3;
array temp {3} _temporary_;
output;
if first.var
then do i = 1 to 3;
temp{i} = invars{i};
end;
else do i = 1 to 3;
temp{i} + invars{i};
end;
if last.var
then do;
do i = 1 to 3;
invars{i} = temp{i};
end;
var1 = 'Tot';
var = '';
output;
end;
drop i;
run;
proc print data=want;
run;
Result:
Beob. var var1 grp1 grp2 grp3 1 best A 3 4 6 2 best B 4 1 7 3 best C 1 3 1 4 Tot 8 8 14 5 Worst G 5 7 1 6 Worst E 5 2 6 7 Tot 10 9 7
Simple way to you. Sort the data set by VAR. I have upcased b for keeping the order of Var after Sorting. Then at the end of each Var, write out new record with sums:
data have;
input Var$ Var1$ grp1 grp2 grp3;
cards;
Best A 3 4 6
Best B 4 1 7
Best C 1 3 1
Worst G 5 7 1
Worst E 5 2 6
;
run;
proc sort data = have;
by Var;
run;
data want;
do until(last.Var);
set have;
by Var;
output;
t1 + grp1; t2 + grp2; t3 + grp3;
end;
Var = ' ';
Var1 = 'Tot';
grp1 = t1; grp2 = t2; grp3 = t3;
output;
call missing(of t:);
drop t:;
run;
proc print data = want;
run;
PROC SUMMARY can make that easy.
data have;
input var $ var1 $ grp1 grp2 grp3;
datalines;
best A 3 4 6
best B 4 1 7
best C 1 3 1
Worst G 5 7 1
Worst E 5 2 6
;;;;
run;
proc print;
run;
proc summary chartype descendtypes;
by var notsorted;
class var1;
output out=test sum(grp:)=;
run;
proc print;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.