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;
... View more