Search code examples
sumsasdatastep

sum over rows split between two columns

my data looks like this and I cant figure out how to obtain the column "want". I've tried various combinations of retain, lag and sum functions with no success unfortunately.

month   quantity1   quantity2   want
1       a           x           x+sum(b to l)
2       b           y           sum(x to y)+sum(c to l)
3       c           z           sum(x to z)+sum(d to l)
4       d       
5       e       
6       f       
7       g       
8       h       
9       i       
10      j       
11      k       
12      l

Thank you for any help on this matter

Solution

  • It is convenient to sum quantity1 and then store value to macro variable. Use superfluous' data example:

    proc sql;
   select sum(qty1) into:sum_qty1 from temp;
quit;

data want;
   set temp;
   value1+qty1;
   value2+qty2;
   want=value2+&sum_qty1-value1;
   if missing(qty2) then want=.;
   drop value:;
run;