How to take the create a variable where the next observation is conditioned on its preceding one?

Shradha1 — Wed, 16 Jun 2021 07:09:11 GMT

I have a dataset with 2 variables Amount and Balance. And I want to create a third variable balance_cf:

Amount Balance
109.98 85358.75
25.00 85358.75
-4500.00 85358.75
941.60 85358.75
104.00 85358.75
215.00 85358.75
110.00 85358.75

In the new dataset, Balance_cf will be equal to Balance for the first observation. For the second observation onwards, Balance_cf will be equal to its preceeding observation if amount>=0, else if amount<0, then balance_cf= lag(balance_cf)+amount.

The resultant dataset will look like this:

Amount Balance Balance_cf
109.98 85358.75 85358.75
25.00 85358.75 85358.75
-4500.00 85358.75 80858.75
941.60 85358.75 80858.75
104.00 85358.75 80858.75
215.00 85358.75 80858.75
110.00 85358.75 80858.75
47.72 85358.75 80858.75
-104.00 85358.75 80754.75

How do I achieve this? Thanks in advance for the help!

Re: How to take the create a variable where the next observation is conditioned on its preceding one

japelin — Wed, 16 Jun 2021 07:22:24 GMT

how about this code.

data have;
 input Amount   Balance;
datalines;
109.98      85358.75
25.00        85358.75
-4500.00   85358.75
941.60      85358.75
104.00     85358.75
215.00     85358.75
110.00     85358.75
47.72     85358.75
-104.00     85358.75
;
run;
data want;
  set have;
  retain Balance_cf;
  if _n_=1 then Balance_cf = Balance;
  else if amount<0 then balance_cf = balance_cf + amount;
run;

topic How to take the create a variable where the next observation is conditioned on its preceding one? in SAS Programming

How to take the create a variable where the next observation is conditioned on its preceding one?

Re: How to take the create a variable where the next observation is conditioned on its preceding one