DATA Step, Macro, Functions and more

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

Reply
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!

 

AcctPerf1Status1Amt1Perf2Status2Amt2Perf3Status3Amt3Cume Sum
123Jan-14Open10Feb-14Open15Mar-14Closed1525
PROC Star
Posts: 1,282

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;
PROC Star
Posts: 8,091

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);

 

 

 

PROC Star
Posts: 8,091

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

Ask a Question
Discussion stats
  • 4 replies
  • 141 views
  • 0 likes
  • 3 in conversation