SAS: sum of multiple rows and looping through all of them

Question

I would like to generate a new column that can be derived from an existing one as follows:

Row 1 of the new column is the sum of all entries from the first 21 rows of the given column.

Row 2 is again the sum of all entries from the next 21 rows (starting from row 2 of the already given column).

and so on.

small example:

 GivenColumn       NewColumn   
-----------------------------
  1                    15          
  2                    14        
  3                    12        
  4                     9        
  5                     5       

Is there an easy way to do this? In Python, I would loop through all rows and thus achieve my desired result.

Thank you!

Answer 1

A forward rolling sum can be computed by using two separate SET statements reading the same data set and storing the forward elements in a cyclically indexed temporary array.

Example (updated):

For 21 items, change the 3's to 21's.

Change then %let N= from 1 to 10 and submit to convince yourself.

data have;
  call streaminit(231108);
  do id = 1 to 6;
    x = rand('integer',10);
    x = id;
    output;
  end;
  format id x 6.;
run;

%let N = 3 ;

data want(keep=x xfrsum&N);
  set have ;

  label xfrsum&N = "x forward rolling sum &";

  array xs[&N] _temporary_ ;

  if _n_ = 1 then do;
    do _n_ = 1 to &N while(not eoforward);
      link xf;
    end;
    if eoforward then _k_ = dim(xs);
  end;

  xfrsum&N = sum(of xs(*));

xf:
  _k_ + 1;
  if _k_ > dim(xs) then _k_ = 1;

  if not eoforward then do ;
    set have (rename=x=xf) end=eoforward;
    xs(_k_) = xf;
  end ;
  else
    xs(_k_) = .;
run;