If you have an ID variable, then you need a way to prevent the initial sums of one ID from being contaminated by data from the prior ID - i.e., whenever you start a new ID, the sum has to be reset to zero or missing: And in addition, while the rolling sum requires subtracting the lag12(A) value, it should do so only when the lag12(region) is the same as the current region. I.e.:
data want;
set have;
by region;
if first.region then sum_a=.;
sum_a + a + -1*ifn(lag12(region)=region,lag12(a),0);
run;
This uses the following features of SAS
- The BY statement tells sas to expect the data to be sorted, and to establish 2 dummy variables (first.region and last.region) that inform you whether the observations in hand is the start or end of a distinct region.
- Statements like
variable + expression + expression + ...
(note the lack of an equals sign) are SUM statement which (a) add the values of the variable (and the expressions - which themselves can be variables) and put the result in the variable. It also tells SAS to retain the variable, enabling your rolling sum.
- The IFN function tests the first argument. If true (i.e. the current obs is the 13th or later within a region), then IFN returns the 2nd argument: lag12(a). Otherwise it returns the 3rd argument (0).
But you ask about doing this for a set of variables (say A, B, and C). The natural SAS approach is to declare A B and C as an array, and to define an analogous array with variables SUMA, SUMB, and SUMC. You might be tempted to do this, which just puts the sum statement (and it associated IFN and LAG functions) inside a loop over the 3 variables A, B, C.
data want;
set have;
by region;
array vars {3} a b c;
array sums {3} suma sumb sumc;
if first.region then call missing(of sums{*});
do v=1 to 3;
sums{v} + vars{v} + -1*ifn(lag12(region)=region,lag12(vars{v}),0);
end;
run;
But this would give the wrong result, because the "LAG12(region)" function would be run 3 times for each operation. The LAG function is a FIFO queue updater, so in this case it would step through the underlying history of region values 3 times as fast as you want. The best option is to take the LAG12(region) evaluation outside of the loop:
data want (drop=_LGREG12 v);
set have;
by region;
array vars {3} a b c;
array sums {3} suma sumb sumc;
if first.region then call missing(of sums{*});
_LGREG12=lag12(region);
do v=1 to 3;
sums{v} + vars{v} + -1*ifn(_LGREG12=region,lag12(vars{v}),0);
end;
run;
regards,
Mark
