Search code examples
sumsaslag

Create a variable based on sum of two variables (one lag)


I have a data set like the one below, where the amount has dropped off, but the adjustment remains. For each row amount should be the sum of the previous amount and the adjustment. So, amount for observation 5 is 134 (124+10).

I have an answer which gets me the next value, but I need some sort of recursion to get me the rest of the way there. What am I missing? Thanks.

data have;
    input amount adjust;
cards;
100 0
101 1
121 20
124 3
. 10
. 4
. 3
. 0
. 1
;
run;

data attempt;
    set have;
    x=lag1(amount);
    if amount=. then amount=adjust+x;
run;

data want;
    input amount adjust;
cards;
100 0
101 1
121 20
124 3
134 10
138 4
141 3
141 0
142 1
;
run;

EDIT:

Also trying something like this now, still not quite what I want.

%macro doodoo;
%do i = 1 %to 5;

data have;
    set have;
/*  if _n_=i+4 then*/
    amount=lag1(amount)+adjust;
run;

%end;
%mend;
%doodoo;

Solution

  • No need to LAG() use RETAIN instead.

    data want ;
      set have ;
      retain previous ;
      if amount = . then amount=sum(previous,adjust);
      previous=amount ;
    run;