sum of values column wise

[Vijay77]

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.

[Jagadishkatam · Accepted solution]

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;

[Vijay77]

it Worked thank you cheers 😊

[Jagadishkatam]

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;

[Kurt_Bremser]

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 

[KachiM]

@Vijay77 

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;

[data_null__]

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;