topic Re: how to sum row by row for a condition in SAS Programming

how to sum row by row for a condition

chithra — Fri, 30 Nov 2018 05:18:46 GMT

Dear All,

I have a dataset like the below :

A1	A	B
IME_ANNUI	4	501
IME_ANNUI	2	200
IME_PREM	11	101
IME_TOTAL	11	100
IME_TOTAL	3	102

and I need the output as follows keeping A1 in ascending and A in descending order and B as sum of(B) with respect to each row.

A1	A	B
IME_ANNUI	4	501
IME_ANNUI	2	701
IME_PREM	11	101
IME_TOTAL	11	100
IME_TOTAL	3	202

Could anyone please help me here?

Thanks,

Chithra

Re: how to sum row by row for a condition

andreas_lds — Fri, 30 Nov 2018 06:04:44 GMT

Please post input data as datastep using datalines-statement, so the we start with something that matches your data.

This is one solution:

data work.have;
   length A1 $ 20 A B 8;

   input A1 A B;

   datalines;
IME_ANNUI 4 501
IME_ANNUI 2 200
IME_PREM 11 101
IME_TOTAL 11 100
IME_TOTAL 3 102
;
run;

proc sort data=work.have out=work.haveSorted presorted;
   by A1 descending A;
run;


data work.want;
   set have;
   by A1;

   length bSum 8;
   retain bSum;

   if first.A1 then do;
      bSum = 0;
   end;

   bSum = bSum + B;

   keep A1 A bSum;
   rename bSum = B;
run;

Re: how to sum row by row for a condition

mkeintz — Fri, 30 Nov 2018 06:11:19 GMT

You want to maintain a cumulative sum of B, resetting it at the start of each A1:

data have;
  input A1 $9.	A	B;
datalines;
IME_ANNUI	4	501
IME_ANNUI	2	200
IME_PREM	11	101
IME_TOTAL	11	100
IME_TOTAL	3	102
run;
data want (drop=sumb);
  set have;
  by a1;
  sumb+b;
  if not first.a1 then b=sumb;
  else sumb=b;
run;

Re: how to sum row by row for a condition

kiranv_ — Fri, 30 Nov 2018 06:14:48 GMT

if your data is already sorted you can do

data want(rename=(_B=B));
set have;
by A1;
if first.A1 then _B= B;
else _B+B;
drop B;
run;

Re: how to sum row by row for a condition

chithra — Fri, 30 Nov 2018 07:22:30 GMT

Thanks..This works