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 |
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.]
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.
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 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.
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));
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.