## How to calculate cumulative sum and exit do loop when condition is met

Occasional Contributor
Posts: 5

# How to calculate cumulative sum and exit do loop when condition is met

Hello All,

I have a dataset where each row corresponds to 1 account with multiple dates and amounts. I want to calculate a cumulative amount per account, and exit iterating thought the rest of the dates when a certain condition is met.

I have a simple snapshot below, in this case I want to cumulate Amt1-Amtn as long as Statusn is not Closed. So I want the Cume Sum to be 25, not 40.

I tried the %goto leave - but my code is still iterating through all the periods corresponding to a record, instead of exiting out of the do loop.

Any help is much appreciated!

 Acct Perf1 Status1 Amt1 Perf2 Status2 Amt2 Perf3 Status3 Amt3 Cume Sum 123 Jan-14 Open 10 Feb-14 Open 15 Mar-14 Closed 15 25
Super User
Posts: 2,061

## Re: How to calculate cumulative sum and exit do loop when condition is met

``````data have;
input Acct	Perf1 \$	Status1	\$ Amt1	Perf2 \$	Status2 \$	Amt2	Perf3	\$Status3 \$	Amt3	;
datalines;
123	Jan-14	Open	10	Feb-14	Open	15	Mar-14	Closed	15	25
;

data want;
set have;
array s(*) status:;
array amt(*) amt:;
k=whichc('Closed',of s(*));
do _n_=1 to k-1;
cumu_sum=sum(cumu_sum,amt(_n_));
end;
run;``````
Super User
Posts: 8,216

## Re: How to calculate cumulative sum and exit do loop when condition is met

Mark @novinosrin's suggestion as the solution, but note that you don't need to create the counter variable K. You could just use:

```data have; input Acct Perf1 \$ Status1 \$ Amt1 Perf2 \$ Status2 \$ Amt2 Perf3 \$ Status3 \$ Amt3 ; datalines;123 Jan-14 Open 10 Feb-14 Open 15 Mar-14 Closed 15 25;data want; set have; array s(*) status:; array amt(*) amt:; do _n_=1 to whichc('Closed',of s(*))-1; cumu_sum=sum(cumu_sum,amt(_n_)); end;run;
```

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 5

## Re: How to calculate cumulative sum and exit do loop when condition is met

I was trying to simplify my question but I guess I didn't help myself. There is actually more than 1 terminal condition. Below is a version of my code. I tried to use %goto to exit out of iterating through the rest of a record when a terminal condition was met, but that didn't work for me. With the code below, I don't think the macro variable i is getting set right, because the code is creating perf_month_month5 and status_month5 when in fact it should stop at 4, and so once again the records are getting flagged incorrectly.

I need a macro do loop, where the loop is over 1 row, multiple columns, and not over multiple rows.

%macro CalcRates(begdate, enddate);

%let start=%sysfunc(inputn(&begdate,anydtdte9.));

%let end=%sysfunc(inputn(&enddate,anydtdte9.));

/*determines the number of months between the two dates*/

%let dif=%sysfunc(intck(month,&start,&end));

%put dif=&dif.;

data Pmt_test;

set Pmtraw;

cumePmt=0;

%let i=1;

%do %while(&i<&dif);

if Pmt_month&i. = . then Pmt_month&i. = 0;

cumePmt = cumePmt + Pmt_month&i.;

if perf_month = &end. then do; /* terminal condition 1*/

flag = 'Last cohort of ref data';

%let i=%eval(&dif+1);

end;

else if total <= 0 then do; /* terminal condition 2*/

flag = 'Bal_at_obs eq 0';

%let i=%eval(&dif+1);

end;

else do;

if strip(Status_month&i.) in ('Closed') /* terminal condition 3*/

then do;

flag = 'Closed in current month';

%let i=%eval(&dif+1);

end;

else if perf_month_month&i = . then do; /* terminal condition 4*/

flag = 'End of reference data';

%let i=%eval(&dif+1);

end;

else if cumePmt >= total then do; /* terminal condition 5*/

flag = 'Cumt Pmt > Bal';

%let i=%eval(&dif+1);

end;

end;

%end;

run;

%mend CalcRates;

%CalcRates(31JAN2014, 31MAY2014);

Super User
Posts: 8,216

## Re: How to calculate cumulative sum and exit do loop when condition is met

It would help if you provide two example datasets: one representing what you have and one representing what you want.

I doubt if you need a macro unless you are going to repeat the task using different dates.

Do you really want to reinitialize the sum if you come across a missing value?

Art, CEO, AnalystFinder.com

Discussion stats
• 4 replies
• 408 views
• 0 likes
• 3 in conversation