Reset cumulative sum in do loop

[_Zack_]

Hi all,

I'm having an issue while trying to reset a cumulative sum after hitting some condition (reset ACCUM_VALUE to 0 after EVENT = 1 occurred), here is my code to reproduce the problem.

Data work.test;

do iter = 0 to 20 by 1;

FREQ = 3;
MTH_TO_NEXT_EVENT = 3;

NO_OF_PMT = FLOOR(sum((iter - MTH_TO_NEXT_EVENT)/FREQ, 1));
TAG = CEIL(sum((iter <= MTH_TO_NEXT_EVENT)*(MTH_TO_NEXT_EVENT),(iter > MTH_TO_NEXT_EVENT)*sum(MTH_TO_NEXT_EVENT, (sum(NO_OF_PMT, -1)*FREQ))));
VALUE = iter + 1;

if iter = 0 then EVENT = 0;
else if iter = TAG then EVENT = 1;
else if iter ne TAG and iter > 0 then EVENT = 0;

if iter = 0 then ACCUM_VALUE = 0;
else if iter = TAG then ACCUM_VALUE = sum(ACCUM_VALUE, VALUE);
else if lag(iter) = TAG then ACCUM_VALUE = sum((ACCUM_VALUE = 0), VALUE);
else if iter ne TAG and iter > 0 then ACCUM_VALUE = sum(ACCUM_VALUE, VALUE);

output;
end;

run;

Results generated from above code:

EVENT	VALUE	ACCUM_VALUE
0	1	0
0	2	2
0	3	5
1	4	9
0	5	14
0	6	20
1	7	27
0	8	35
0	9	44
1	10	54
0	11	65
0	12	77
1	13	90
0	14	104
0	15	119
1	16	135
0	17	152
0	18	170
1	19	189
0	20	209
0	21	230

The expected result as below:

EVENT	VALUE	ACCUM_VALUE
0	1	0
0	2	2
0	3	5
1	4	9
0	5	5
0	6	11
1	7	18
0	8	8
0	9	17
1	10	27
0	11	11
0	12	23
1	13	36
0	14	14
0	15	29
1	16	45
0	17	17
0	18	35
1	19	54
0	20	20
0	21	41

[ballardw]

What is the rule based on values in the data to reset the value?

[_Zack_]

Hi @ballardw 

Thanks for reply, i had make some amendment on the code. Added an EVENT flag to indicate when a event will happened, my intention is to create a cumulative sum for VALUE happened before or on EVENT tag, then reset the ACCUM_VALUE = 0 after EVENT happened so that it can continue to add up VALUE for next EVENT.

[ballardw · Accepted solution]

This creates the desired example output.

data have;
   input EVENT	VALUE;
datalines;
0	1	
0	2	
0	3	
1	4	
0	5	
0	6	
1	7	
0	8	
0	9	
1	10	
0	11	
0	12	
1	13	
0	14	
0	15	
1	16	
0	17	
0	18	
1	19	
0	20	
0	21	
;

data want;
   set have;
   retain accum_value;
   if _n_=1 then accum_value=0;
   else accum_value = sum(accum_value,value);
   output;
   if event then accum_value=0;
run;

Some times it is easier to break a problem into two parts: create the "event" "value" data and then process with a second data step (or sometimes another procedure)

The "trick" in the above code is that when an OUTPUT statement is used then the normal "automatic output at the bottom of the data step" is disabled and you would need to add another explicit Output to duplicate that. If you are doing other stuff in the same data step order of calculations gets to be very important with some approaches to a problem.

Your variable NO_OF_PMT makes me suspect the actual underlying problem may be some sort of financial transaction. You might investigate the SAS FINANCE or maybe the PMT functions. There are many financial functions available and if that is what this is related to you may save a lot of headache with explicit coding of loops and such when you may be able to use a single function call.

[_Zack_]

Thanks for the explanation and good trick!