03-12-2018 05:42 PM
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!
03-12-2018 05:52 PM
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;
03-12-2018 06:04 PM
Mark @novinosrin's suggestion as the solution, but note that you don't need to create the counter variable K. You could just use:
input Acct Perf1 $ Status1 $ Amt1 Perf2 $ Status2 $ Amt2 Perf3 $ Status3 $ Amt3 ;
123 Jan-14 Open 10 Feb-14 Open 15 Mar-14 Closed 15 25
array s(*) status:;
array amt(*) amt:;
do _n_=1 to whichc('Closed',of s(*))-1;
Art, CEO, AnalystFinder.com
03-12-2018 07:05 PM
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);
/*determines the number of months between the two dates*/
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';
else if total <= 0 then do; /* terminal condition 2*/
flag = 'Bal_at_obs eq 0';
if strip(Status_month&i.) in ('Closed') /* terminal condition 3*/
flag = 'Closed in current month';
else if perf_month_month&i = . then do; /* terminal condition 4*/
flag = 'End of reference data';
else if cumePmt >= total then do; /* terminal condition 5*/
flag = 'Cumt Pmt > Bal';
03-12-2018 07:41 PM
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