sum over rows split between two columns

Question

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

score 2 · Accepted Answer · answered Jul 08 '16 at 18:04

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;

score 1 · Answer 2 · answered Jul 08 '16 at 15:48

You may be able to do this in one step, but the following produces the desired result in two. The first step is to calculate the sum of the relevant quantity1 values, and the second is to add them to the sum of the relevant quantity2 values:

 data temp;
    input month qty1 qty2;
    datalines;
        1 1 100
        2 1 100
        3 1 100
        4 1 .
        5 1 .
        6 1 .
        7 1 .
        8 1 .
        9 1 .
        10 1 .
        11 1 .
        12 1 .
    ;
run;

proc sql;
    create table qty1_sums as select distinct
        a.*, sum(b.qty1) as qty1_sums
        from temp as a
        left join temp as b
        on a.month < b.month
        group by a.month;

    create table want as select distinct
        a.*,
        case when not missing(a.qty2) then sum(a.qty1_sums, sum(b.qty2)) end as want
        from qty1_sums as a
        left join temp as b
        on a.month >= b.month
        group by a.month;
quit;

score 1 · Answer 3 · answered Jul 08 '16 at 17:18

Sounds like a 'rolling 12 months sum'. If so, much easier to do with a different data structure (not 2 variables, but 24 rows 1 variable); then you have all of the ETS tools, or a simple process in either SQL or SAS data step.

If you can't/won't restructure your data, then you can do this by loading the data into temporary arrays (or hash table but arrays are simpler for a novice). That gives you access to the whole thing right up front. Example:

data have;
  do month = 1 to 12;
    q_2014 = rand('Uniform')*10+500+month*5;
    q_2015 = rand('Uniform')*10+550+month*5;
    output;
  end;
run;

data want;
  array q2014[12] _temporary_;       *temporary array to hold data;
  array q2015[12] _temporary_;
  if _n_=1 then do;                  *load data into temporary arrays;
    do _n = 1 to n_data;
      set have point=_n nobs=n_data;
      q2014[_n] = q_2014;
      q2015[_n] = q_2015;
    end;
  end;
  set have;
  do _i = 1 to _n_;                          *grab the this_year data;
    q_rolling12 = sum(q_rolling12,q2015[_i]);
  end;
  do _j = _n_+1 to n_data;
    q_rolling12 = sum(q_rolling12,q2014[_j]);*grab the last_year data;
  end;
run;

sum over rows split between two columns

3 Answers3