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

Question

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;

The reason that your lag approach doesn't work is that lag returns the previous value as last read in from the set statement, not value as it was at the end of the previous iteration of the data step. — user667489
– user667489, Commented Aug 14, 2015 at 19:22

Tom · Accepted Answer · 2015-08-14 19:17:07Z

1

No need to LAG() use RETAIN instead.

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

answered Aug 14, 2015 at 19:17

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pyll Over a year ago

Ok, it was the reassignment of the variable I was missing. Big help. Thanks, again!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related