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)
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.