topic Re: How to calculate cumulative sum and exit do loop when condition is met in SAS Programming

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

PS185 — Mon, 12 Mar 2018 21:42:35 GMT

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

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

novinosrin — Mon, 12 Mar 2018 21:52:51 GMT

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;

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

art297 — Mon, 12 Mar 2018 22:04:03 GMT

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

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

PS185 — Mon, 12 Mar 2018 23:05:44 GMT

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;

run;

%mend CalcRates;

%CalcRates(31JAN2014, 31MAY2014);

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

art297 — Mon, 12 Mar 2018 23:41:31 GMT

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