I think you are saying:
- For all refunds (negative trnf_amt) you want the DAYS_LAST_TRNS variable to be the refund transaction date minus the transaction date of the most recent non-refunded transaction.
- For all other records N_DAYS is just the number of days from transaction to month end.
If your date variables (per @PaigeMiller's question) are numeric data values, and if all refunds immediately follow the corresponding transaction (my question), then you can keep track of an intermediate variable (_last_non_refund_date), which you retain across observations. If the current transaction is not a refund, use TRNS_DATE. But if it is, use _last_non_refund_date:
data have;
input MTH_END :date9. TRNS_DTE :date9. NAME :$3. TRNF_AMT expected_DAY_LAST_TRNS ;
format trns_dte mth_end date9.;
datalines;
31May2001 04May2001 ABC 10000.000000 27
31Jan2012 17Jan2012 ABC 3564.000000 14
31Jan2013 30Jan2013 ABC 3564.000000 1
31Dec2013 31Dec2013 ABC 4000.000000 0
31Jan2015 19Jan2015 ABC 4000.000000 12
29Feb2016 16Feb2016 ABC 4000.000000 13
28Feb2017 15Feb2017 ABC 4000.000000 13
28Feb2018 03Feb2018 ABC 2000.000000 25
31Jul2018 27Jul2018 ABC 2000.000000 4
31Jan2019 25Jan2019 ABC 2000.000000 6
30Sep2019 02Sep2019 ABC 5300.000000 28
30Sep2019 02Sep2019 ABC -5300.000000 248
run;
data want (drop=_:);
set have (keep=name);
by name;
merge have
have (firstobs=2 keep=trnf_amt rename=(trnf_amt=_nxt_trnf_amt));
retain _last_non_ref_date .;
if first.name then _last_non_ref_date= .;
if trnf_amt>0 and _nxt_trnf_amt>0 then _last_non_ref_date=trns_dte;
new_DAY_LAST_TRNS = mth_end - ifn(trnf_amt>0,trns_dte,_last_non_ref_date);
run;
- I use the MERGE statement to provide a device for looking ahead one record, which is how I determine whether the record-in-hand is going to be refunded. If so, I don't update the _last_non_ref_date variable.
- I also use the SET statement, to provide a basis against which the BY statement can be used.
- This program reads in your expected variable, so you can see it side by side with my calculated variable.
- I repeat - this program assumes refunds always immediately follow the corresponding transaction. Otherwise more programming is required to detect a transaction that will be ultimately refunded.
Unresolved: what do you want if the very first transaction is refunded in the second transaction? In my program the resulting DAYS_LST_TRNS is a missing value for the refund transaction.