The sum of 3 fixed payments met date within any 1 to 3 consecutive cycle end dates.

analyst007 — Thu, 11 Apr 2024 00:50:54 GMT

acct_no	fix_paymt	txn_amt	txn_dt	cyc_end	cyc_no	x3_paymt_met	x3_paymt_met_dt
123	10	10	24-Jan	25-Jan	1	0	.
123	10	10	24-Feb	25-Feb	2	0	.
123	10	10	27-Apr	25-May	5	0	.
123	10	10	28-Apr	25-May	5	0	.
123	10	10	24-Jun	25-Jun	6	1	25-Jun
123	10	10	25-Jul	25-Jul	7	1	25-Jul

Hello - I have thousands of customers' transaction data like the above until the cyc_no column and I want to get the next two columns:

x3_paymt_met: 3 payments met tag

x3_paymt_met_dt: 3 paymnets met date

What do I need? - Find the first transaction date when a customer made the sum of 3 fixed payments within 1 to 3 consecutive cycle end dates. (We need to consider the missing (no payment) cycle as well)

In the above example, customer 123 first met the criteria on 25 Jun as they paid at least $30 (3 * fixed pay) within 3 consecutive cycles 4,5, and 6.
The customer didn't meet this criterion in the first 3 cycles 1,2, and 3. Then, we consider the transaction from the next cycle i.e. from 2 to 4.
Now, we sum the transactions made in cycles 2 to 4. The customer didn't meet the criteria here either.
Similarly, transactions made in cycles 3 to 5. The customer didn't meet the criteria here either.
Likewise, transactions made cycles 4 to 6. The customer finally met the criteria here.
Likewise, transactions made in cycles 5 to 7. The customer met the criteria here as well.

Rules:

Customers can pay any amount like $1, or $2.5 etc

They can make multiple payments within the same cycle.

Can someone please help me code this in SAS/SQL? I am new to SAS programming and not exactly sure how to loop or is there some way to achieve the outcome w/o using loop?

Thank you so much for making it through to the end!

What I have so far is:

data want;
 set above_example;
 by acct_no;
 %j = 1;
 where cyc_no between &j and &j+2;
 find cumulative sum (cuml);
 if cuml >= 3 * fixed pay then do;
  x3_payments_met = 1;
  x3_payments_met_dt = min(txn_dt); end;
 elif cuml < 3*fixed pay then do;
  x3_payments_met = 0;
 j = j+1;
run;

Re: The sum of 3 fixed payments met date within any 1 to 3 consecutive cycle end dates.

mkeintz — Fri, 12 Apr 2024 18:38:27 GMT

If the data are sorted by acct_no/cyc_no, then the programming is easy with a DATA step. All you need to determine is whether the cyc_no value for the current qualifying obs is no more than two greater than the second prior qualifying obs (and has the same acct_no). By "qualifying" I mean obs with a positive payment.

If this is true then:

data want;
  set have;
  by acct_no;
  flag=0;
  if fix_paymt>0 then do;
    if lag2(acct_no)=acct_no and lag2(cyc_no)>=cyc_no-2 then flag=1;
  end;
run;

topic Re: The sum of 3 fixed payments met date within any 1 to 3 consecutive cycle end dates. in SAS Programming

The sum of 3 fixed payments met date within any 1 to 3 consecutive cycle end dates.

Re: The sum of 3 fixed payments met date within any 1 to 3 consecutive cycle end dates.