topic Re: sum of values column wise in SAS Programming

sum of values column wise

Vijay77 — Wed, 30 Oct 2019 10:54:40 GMT

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.

Re: sum of values column wise

Jagadishkatam — Wed, 30 Oct 2019 11:08:25 GMT

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;

Re: sum of values column wise

Jagadishkatam — Wed, 30 Oct 2019 11:13:45 GMT

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;

Re: sum of values column wise

Vijay77 — Wed, 30 Oct 2019 11:23:24 GMT

it Worked thank you cheers 😊

Re: sum of values column wise

Kurt_Bremser — Wed, 30 Oct 2019 11:29:44 GMT

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

Re: sum of values column wise

KachiM — Wed, 30 Oct 2019 11:36:46 GMT

@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;

Re: sum of values column wise

data_null__ — Wed, 30 Oct 2019 15:51:47 GMT

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;