BookmarkSubscribeRSS Feed
PS185
Calcite | Level 5

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
4 REPLIES 4
novinosrin
Tourmaline | Level 20
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;
art297
Opal | Level 21

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

 

PS185
Calcite | Level 5

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

 

 

 

art297
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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