Using the previous result line to calcute the next

[Augusto]

Hello,

I need an help.

I have to calculated a result using the previous result.

HAVE:

month_base	segment	safra	account	attrition
31/01/2019	4k	1	54,00	2%
28/02/2019	4k	2	54,00	2%
31/03/2019	4k	3	54,00	2%
30/04/2019	4k	4	53,00	3%
31/01/2019	Affluent	1	75,00	2%
28/02/2019	Affluent	2	75,00	2%
31/03/2019	Affluent	3	75,00	2%
30/04/2019	Affluent	4	75,00	4%

WANT:

 To create a new column called account_without_attrition:  This is calculated by the previous result :

month_base	segment	safra	account	attrition	account_without_attrition
31/01/2019	4k	1	54,00	2%	52,92
28/02/2019	4k	2	54,00	2%	51,84
31/03/2019	4k	3	54,00	2%	50,76
30/04/2019	4k	4	53,00	3%	49,17
31/01/2019	Affluent	1	75,00	2%	47,67
28/02/2019	Affluent	2	75,00	2%	46,17
31/03/2019	Affluent	3	75,00	2%	44,67
30/04/2019	Affluent	4	75,00	4%	41,67

[ballardw]

So what exact calculation gets a value of 52,92 for the first row? The second row? The fifth row where the account value changes?

You need to show any formula used as the number shown is not immediately obvious.

[FreelanceReinh · Accepted solution]

Hello @Augusto,

As a start, try this:

data have;
input month_base :ddmmyy. segment $ safra account :numx. attrition percent.;
format month_base ddmmyy10. account numx12.2 attrition percent.;
cards;
31/01/2019 4k 1 54,00 2%
28/02/2019 4k 2 54,00 2%
31/03/2019 4k 3 54,00 2%
30/04/2019 4k 4 53,00 3%
31/01/2019 Affluent 1 75,00 2%
28/02/2019 Affluent 2 75,00 2%
31/03/2019 Affluent 3 75,00 2%
30/04/2019 Affluent 4 75,00 4%
;

data want;
set have;
retain acc_wo_attr;
if _n_=1 then acc_wo_attr=round(account*(1-attrition),1e-9);
else acc_wo_attr=round(acc_wo_attr-account*attrition, 1e-9);
format acc_wo_attr numx12.2;
label acc_wo_attr='account without attrition';
run;

proc print data=want noobs label;
run;

[Edit: corrected typo.] 

[Augusto]

Hi.. sorry, i forgot a condition.
If safra eq 1 then account_whitout_attrition = account - (account * attrition);
Else account_whitout_attrition = (previous result) - (account * attrition);

[FreelanceReinh]

---

@Augusto wrote:
Hi.. sorry, i forgot a condition.
If safra eq 1 then account_whitout_attrition = account - (account * attrition);
Else account_whitout_attrition = (previous result) - (account * attrition);

---

No problem. Just change the IF condition in my code from _n_=1 to safra=1. Needless to say that this will change the results for obs. 5 through 8.

[noling]

Yes, it's not clear exactly what you want or your logic, but I would guess you want something like this?

retain value 0;
if _N_=1 then value=account;
value = value*(1-(attrition*.01));