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 |
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;
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
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);
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.