topic Re: how to calculate sum group wise in SAS Programming

how to calculate sum group wise

thanikondharish — Tue, 28 Aug 2018 05:57:10 GMT

data s1 ;
input id $ dose ;
cards ;
101 22
101 23
101 24
102 43
102 23
102 11
102 22
103 22
103 22
;

i have date like see above

i want to data keep like see below

group(id) wise sum

id	dose	total
101	23	69
101	24	.
101	22	.
102	11	99
102	22	.
102	23	.
102	43	.
103	22	44
103	22	.

Re: how to calculate sum group wise

Kurt_Bremser — Tue, 28 Aug 2018 06:50:35 GMT

Create a separate sums table, and do a merge with a twist:

data have;
input id $ dose;
cards;
101 22
101 23
101 24
102 43
102 23
102 11
102 22
103 22
103 22
;
run;

proc sql;
create table sums as
select id, sum(dose) as total
from have
group by id;
quit;

data want;
merge
  have
  sums
;
by id;
if not first.id then total = .;
run;

proc print data=want noobs;
run;

Result:

id     dose    total

101     22       69 
101     23        . 
101     24        . 
102     43       99 
102     23        . 
102     11        . 
102     22        . 
103     22       44 
103     22        .

Alternatively, you could reverse the order within the groups, sum and set total at last.id, and reverse the order back to original.

Re: how to calculate sum group wise

shahparth260 — Tue, 28 Aug 2018 07:19:01 GMT

I am doing for you alternative part, by taking reference from @Kurt_Bremser and add little flavor of 
retain.

proc sort data=s1;
 by id descending dose;
run;

data s2;
 set s1;
 by id descending dose;
 retain total ;
 if first.id then total=dose;
 else total+dose;
 if last.id;
 keep id total;
run;

data s2;
merge s2 s1;
by id;
if  first.id then newvar=total;
else newvar=.;
drop total;
rename newvar=total;
run;

Re: how to calculate sum group wise

KachiM — Tue, 28 Aug 2018 07:56:49 GMT

Another way is to use DOW. Two double do-loops get it. The presorted Data Set is assumed.

data want;
   do until(last.id);
      set have;
      by id notsorted;
      sum + dose;
   end;
   do until(last.id);
      set have;
      by id notsorted;
      if not first.id then sum = .;
      output;
  end;
run;

HTH

Cheers

DATASP

Re: how to calculate sum group wise

s_lassen — Tue, 28 Aug 2018 10:26:09 GMT

That can also be done reading the data just once:

data want;
  set s1;
  by id;
  _sum+dose;
  if last.id then do;
    sum=_sum;
    _sum=0;
    end;
  drop _sum;
run;

Re: how to calculate sum group wise

PaigeMiller — Tue, 28 Aug 2018 12:02:08 GMT

You shouldn't have to write your own code to compute group sums. PROC SUMMARY will do this, then you can merge the results into the original data set.

All of the examples above where a DATA step is used to compute the sum have a potential error ... if there is a missing value in the variable to be summed, all the examples above result in a missing sum. This doesn't happen with PROC SUMMARY. (It can also be fixed in the data step if necessary).

Re: how to calculate sum group wise

KachiM — Tue, 28 Aug 2018 13:12:30 GMT

Your observation on the presence of missing values is not true as I have used SUM statement. Here is a test:

data have;
input id $ dose;
cards;
101 22
101 23
101  .
101 24
102 43
102 23
102 11
102 22
103 22
103  .
103 22
;
run;

data want;
   do until(last.id);
      set have;
      by id notsorted;
      sum + dose;
   end;
   do until(last.id);
      set have;
      by id notsorted;
      if not first.id then sum = .;
      output;
  end;
run;

Obs 	id 	dose 	sum
1 	101 	22 	69
2 	101 	23 	.
3 	101 	. 	.
4 	101 	24 	.
5 	102 	43 	99
6 	102 	23 	.
7 	102 	11 	.
8 	102 	22 	.
9 	103 	22 	44
10 	103 	. 	.
11 	103 	22 	.

Re: how to calculate sum group wise

PaigeMiller — Tue, 28 Aug 2018 13:20:08 GMT

Thanks. I was not aware of that.