Hi,
Please someone can solve this issue ?
Its need to calculate the next result using the previous one.
HAVE
segment | month | safra | qt_account_initial | attrition |
varejo | 30/06/2019 | 1 | 100 | 2,00% |
varejo | 31/07/2019 | 2 | 100 | 2,00% |
varejo | 31/08/2019 | 3 | 100 | 2,00% |
affluent | 30/06/2019 | 1 | 80 | 0,00% |
affluent | 31/07/2019 | 2 | 80 | 0,00% |
affluent | 31/08/2019 | 3 | 80 | 0,00% |
WANT
segment | month | safra | qt_account_initial | attrition | new_qt_account | acc_whithout_attrition | attrition_to_new_segment |
varejo | 30/06/2019 | 1 | 100 | 2,00% | 98,00 | 98,00 | 2,00 |
varejo | 31/07/2019 | 2 | 100 | 2,00% | 96,04 | 96,04 | 3,96 |
varejo | 31/08/2019 | 3 | 100 | 2,00% | 94,12 | 94,12 | 5,88 |
affluent | 30/06/2019 | 1 | 80 | 0,00% | 82,00 | 82,00 | 0,00 |
affluent | 31/07/2019 | 2 | 80 | 0,00% | 85,96 | 82,00 | 0,00 |
affluent | 31/08/2019 | 3 | 80 | 0,00% | 91,84 | 82,00 | 0,00 |
The TRICK is : THE attrition_to_new_segment result needs to be added to the AFFLUENT Segment line before the its calculation.
Please, use SAFRA EQ 1 for the condition to the first iteration.
I could get the calculation using the retain, but could not solve the trick.
Reeza, please use the attrition field to decrease.
retain acc_whithout_attrition attrition_to_new_segment;
if safra eq 1 then acc_whithout_attrition = qt_account_initital - (qt_account_initital * attrition);
else acc_whithout_attrition = acc_whithout_attrition - (acc_whithout_attrition * attrition);
for the attrition_to_new_segment, please consider: qt_account_initital - acc_whithout_attrition;
The TRICK is that what is decreased from the AFFLUENT line, needs to be added to the Varejo
@Augusto wrote:
Reeza, please use the attrition field to decrease.
But it's 0% for affluent and is 2% for varejo.
How does that carry over. I see someone else figured it out, so maybe I'm just not familiar with this calculation.
ow... sorry it was a mistake, please consider 2% for varejo also.
@Augusto What Reeza is saying is Affluent is 0% and thus nothing can decrease and thus you can not use this decrease to add to Varejo .. I gave the solution in regard to a very similar thread that you started (but was deleted). When I came across the current thread, I took a cursory look and thought that the input and output is same. That's why I pasted the code below alongwith the Excel file (which was generated from the previous post).
If you can write the requirements in terms of a Pseudo-code (algorithm) and send a snapshot of correct input and output, people here will be able to help you very quickly. Right now there seems to be a confusion (at least I am confused).
Please consider doing so.
Thank you
You can try the below code. I have attached the excel file that was used to generate the result. Place it in the home directory or else ...
Try to change the below code at line 1 and line 4 to suit the path of the excel file and name of the excel sheet. Formatting is not matching exactly but I think results do.
LIBNAME Home_xls xlsx "~/TempDelte.xlsx"; DATA Preprocessed(drop=attrition_a attrition_b perc_affluent); SET Home_xls.Sheet1; INFORMAT segment $10. month date10. safra 8. qt_account_initial 5.; attrition_a = substr(attrition_a,1,length(attrition_a)-1); attrition_b = substr(attrition_b,1,length(attrition_b)-1); perc_affluent = substr(perc_affluent,1,length(perc_affluent)-1); attrition_a_=INPUT(attrition_a, percent5.2); attrition_b_=INPUT(attrition_b, percent5.2); perc_affluent_=INPUT(perc_affluent, percent5.2); RUN; PROC REPORT DATA=Preprocessed; COLUMNS segment month safra qt_account_initial attrition_a_ attrition_b_ perc_affluent_ new_acc_initial acc_whitout_attrition_a acc_whitout_attrition_b acc_whitout_affluent qt_add_affluent; DEFINE segment / DISPLAY format=$10.; DEFINE month / DISPLAY; DEFINE safra / DISPLAY; DEFINE qt_account_initial / DISPLAY format=5.; DEFINE attrition_a_ / DISPLAY format=comma5.2 'attrition_a'; DEFINE attrition_b_ / DISPLAY format=comma5.2 'attrition_b'; DEFINE perc_affluent_ / DISPLAY format=comma5.2 'perc_affluent'; DEFINE new_acc_initial / COMPUTED format=5.2; DEFINE acc_whitout_attrition_a / COMPUTED format=5.2; DEFINE acc_whitout_attrition_b / COMPUTED format=5.2; DEFINE acc_whitout_affluent / COMPUTED format=5.2; DEFINE qt_add_affluent / COMPUTED format=5.2; COMPUTE new_acc_initial; new_acc_initial = _C4_; ENDCOMP; COMPUTE acc_whitout_attrition_a; acc_whitout_attrition_a = _C4_-_C4_*_C5_/100.00; ENDCOMP; COMPUTE acc_whitout_attrition_b; acc_whitout_attrition_b = _C9_-_C9_*_C6_/100.00; ENDCOMP; COMPUTE acc_whitout_affluent; acc_whitout_affluent = _C10_-_C10_*_C7_/100.00; ENDCOMP; COMPUTE qt_add_affluent; qt_add_affluent = _C10_-_C11_; ENDCOMP; RUN; LIBNAME Home_xls CLEAR;
and here is the result.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.