Count day of last transaction

[MK2508]

Hi Guys, can help me with the following scenerio below. I need to derive the number of days for DAY_LAST_TRNS field. If there is a transaction taken place, the calculation of day_last trns field will be MTH_END - TRNS_DTE. However, if there was a refund to net off the previous transaction, we will not consider it as a transaction record. Therefore, we will take the last transaction as the latest record. Please advice what is the code to derive for DAY_LAST_TRNS.

MTH END	TRNS_DTE	NAME	TRNF_AMT	DAY_LAST_TRNS (MTH_END-TRNS_DTE)
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	Days = 30 Sept - 25 Jan

[PaigeMiller]

A couple of questions:

• Are MTH_END and TRNS_DTE both numeric variables? Or is one or both a character variable? Please run PROC CONTENTS on your SAS data set to see.
• Does the refund always happen in the row immediately below the original transaction? Or can the refund show up elsewhere?
• Does the refund always have the exact same TRNF_AMT except it is negative? 
• Is the variable NAME relevant to this problem?

[mkeintz]

Do refund records always immediately follow the refunded transaction?  Or can there be other transactions between them?

[mkeintz]

I think you are saying:

1. 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.
2. 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;

1. 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.
   
   
2. I also use the SET statement, to provide a basis against which the BY statement can be used.
   
   
3. This program reads in your expected variable, so you can see it side by side with my calculated variable.
   
   
4. 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.