topic Re: Using the previous result line to calcute the next in SAS Programming

Using the previous result line to calcute the next

Augusto — Mon, 24 Jun 2019 16:08:12 GMT

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

Re: Using the previous result line to calcute the next

ballardw — Mon, 24 Jun 2019 16:22:36 GMT

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.

Re: Using the previous result line to calcute the next

FreelanceReinh — Tue, 25 Jun 2019 07:02:53 GMT

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.]

Re: Using the previous result line to calcute the next

Augusto — Mon, 24 Jun 2019 16:30:30 GMT

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);

Re: Using the previous result line to calcute the next

noling — Mon, 24 Jun 2019 16:34:14 GMT

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));

Re: Using the previous result line to calcute the next

FreelanceReinh — Mon, 24 Jun 2019 16:34:31 GMT

@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.