topic Calculating Cumulative Total and Date when Criteria Filled proc sql in SAS Programming

Calculating Cumulative Total and Date when Criteria Filled proc sql

ekthomas13 — Wed, 03 Feb 2021 04:07:27 GMT

Hi all,

I'm trying to create a new table that does 2 things with the dataset.

If the account reaches a cumulative minimum deposit of $500 and
In which day the account reached a cumulative minimum deposit of $500.

I'm more comfortable with proc sql but can use data step.

Account	Deposit_Dt	Amount
A12345	Day1	100
A12345	Day5	200
A12345	Day10	300
B12346	Day1	50
B12346	Day6	150
B12346	Day16	250
C12347	Day2	500
C12347	Day5	100
C12347	Day19	50
D12348	Day4	200
D12348	Day28	100

Thanks,

Re: Calculating Cumulative Total and Date when Criteria Filled proc sql

PeterClemmensen — Wed, 03 Feb 2021 05:40:42 GMT

Try this

data have;
input Account $ Deposit_Dt $ Amount;
datalines;
A12345 Day1  100
A12345 Day5  200
A12345 Day10 300
B12346 Day1  50 
B12346 Day6  150
B12346 Day16 250
C12347 Day2  500
C12347 Day5  100
C12347 Day19 50 
D12348 Day4  200
D12348 Day28 100
;

data want(drop = cum r);
   set have;
   by Account;
   if first.Account then do;
      cum = Amount; r = 0;
   end;
   else cum + Amount;
   if cum ge 500 and r = 0 then do;
      flag = 1; r = 1;
   end;
   retain r;
run;