topic CALCULATE THE NEXT RESULT WITH THE PREVIOUS ONE - WITH TRICK in SAS Procedures

CALCULATE THE NEXT RESULT WITH THE PREVIOUS ONE - WITH TRICK

Augusto — Wed, 26 Jun 2019 14:05:08 GMT

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.

Re: CALCULATE THE NEXT RESULT WITH THE PREVIOUS ONE - WITH TRICK

Reeza — Wed, 26 Jun 2019 14:48:27 GMT

What is the rule/logic. Why is the affluent account decreasing and how do you know the rate of decrease/increase? How do youknow that?

Re: CALCULATE THE NEXT RESULT WITH THE PREVIOUS ONE - WITH TRICK

koyelghosh — Wed, 26 Jun 2019 15:14:45 GMT

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.

Correct output but not a matching format

Re: CALCULATE THE NEXT RESULT WITH THE PREVIOUS ONE - WITH TRICK

Augusto — Wed, 26 Jun 2019 15:27:33 GMT

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

Re: CALCULATE THE NEXT RESULT WITH THE PREVIOUS ONE - WITH TRICK

Reeza — Wed, 26 Jun 2019 15:59:57 GMT

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

Re: CALCULATE THE NEXT RESULT WITH THE PREVIOUS ONE - WITH TRICK

Augusto — Wed, 26 Jun 2019 16:16:17 GMT

ow... sorry it was a mistake, please consider 2% for varejo also.

Re: CALCULATE THE NEXT RESULT WITH THE PREVIOUS ONE - WITH TRICK

koyelghosh — Wed, 26 Jun 2019 16:27:39 GMT

@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