topic Re: Count day of last transaction in New SAS User

Count day of last transaction

MK2508 — Wed, 08 Jan 2020 13:31:38 GMT

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

Re: Count day of last transaction

PaigeMiller — Wed, 08 Jan 2020 14:22:30 GMT

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?

Re: Count day of last transaction

mkeintz — Wed, 08 Jan 2020 14:32:19 GMT

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

Re: Count day of last transaction

mkeintz — Wed, 08 Jan 2020 15:50:09 GMT

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.