How to calculate moving total amount for one year with group by facility

[umashankersaini]

Hi Team,

I need to calculate total amount spend by each customer for 365 days where 12 months should be calculated from first purchase made by customer during the campaign window.

i have calculated the first purchase date during the campaign window but unable to pass the dates into where clause.

Kindly have a look into sample data

CPGNID	CUSTOMER_ID	CPGN_STRT_DT	CPGN_END_DT	Tran_dt_During_CPGN
106248162U	A1000000050	27-Dec-19	3-Jan-20	.
106249382U	A1000000050	3-Jan-20	10-Jan-20	.
106252082U	A1000000050	19-Jan-20	26-Jan-20	.
106248382U	A1000001868	28-Dec-19	4-Jan-20	.
106251782U	A1000001868	18-Jan-20	25-Jan-20	.
106248242U	A10000303	27-Dec-19	3-Jan-20	28-Dec-19
106249462U	A10000303	3-Jan-20	10-Jan-20	.
106251502U	A10000303	17-Jan-20	24-Jan-20	.
106248462U	A1000100052	28-Dec-19	4-Jan-20	2-Jan-20
106249462U	A1000100052	3-Jan-20	10-Jan-20	4-Jan-20
106251502U	A1000100052	17-Jan-20	24-Jan-20	.
106248162U	A1000100068	27-Dec-19	3-Jan-20	.
106249382U	A1000100068	3-Jan-20	10-Jan-20	.
106252082U	A1000100068	19-Jan-20	26-Jan-20	.
106183082U	A1000100068	2-Jan-20	9-Jan-20	.
106248462U	A1000100205	28-Dec-19	4-Jan-20	28-Dec-19
106249462U	A1000100205	3-Jan-20	10-Jan-20	.
106252682U	A1000100205	19-Jan-20	26-Jan-20	.

Transaction table would have CUSTOMER_ID, TRAN_DT and TRAN_AMT  (historical to t-1 date)

I could try with :

DATA _null_;

call symput('SD',"'"||(STRIP(put(today()-1,date9.)))||"'d");
call symput('SD12',"'"||(STRIP(put(intnx('Month',today(),-12,'S'),Date9.)))||"'d");

Run;

Proc sql;
create table Revenue  as
select A.CUSTOMER_ID
, SUM(B.TRAN_AMT) AS REV_12M

from TEMP A
left join TRAN_TABLE B
on A.CUSTOMER_ID =B.CUSTOMER_ID
Where B.TRAN_DT GE &SD12. and B.TRAN_DT LE &SD.
Group by 1 ;
Quit;

Here issue is with 'SD' macro variable as it is changing for each customer so i am not able to think about the logic.

Rev_12M get calculated only for those customer who has shopped during that campaign period so customer *303 would have revenue only for one campaign which is 106248242U and same for customer A1000100052 revenue would be different as first time where clause window should be 01JAN2020 -365 days and for second time 03JAN2020 -365.

Kindly help.

Regards,

Uma Shanker Saini

[mkeintz]

Uma:

Precisely because your date ranges change from record to record, using a macrovar approach is very problematic.  I suggest you consider using a DATA step.  It should

1. Read in each customer's records sequentially (i.e. ordered by customer/date-of-transaction). 
2. Capture the first qualifying transaction
3. Use the captured date to establish a 12-month range.  You can RETAIN these values over the rest of that customer's records (which will replace the need for macrovars in this case).
   
   Question:  Do you have any records of transactions OUTSIDE of campaign dates?  I assume not.
   
   
4. Make a new variable  CUM_PRIOR_AMT containing the accumulated TRAN_AMT only for prior qualifying records of that customer.
5. Output only transactions within the dynamically generated 12-month range.

Here is an untested program:

data want (drop=_:);
  set have (where=(tran_dt_during_cpgn^=.));
  by customer_id;

  retain _beg_date _end_date  .;
  if first.customer_id then do;
    _beg_date=tran_dt_during_cpgn;
    _end_date=intnx('month',_beg_date,12,'same');
    cum_prior_amt=0;
  end;
  if _beg_date <= tran_dt_during_cpgn <= _end_date;
  output;
  cum_prior_amt+tran_amt;
run;

Note I use the WHERE clause because I assume that non-missing TRAN_DT_DURING_CPGN records are the only valid candidate transactions for your task.

Because I assume that for each customer, all the non-missing TRAN_DT_DURING_CPGN are in ascending order, then the very first record satisfying the WHERE filter for each customer marks the start of your 12-month time span.

The INTNX function adds 12-moths to the _beg_date to get _end_date.  The "same" parameters tells INTNX to return a day-of-month that is the same as the day-of-month in _BEG_DATE.

The subsetting IF keeps only records during the retained 12-month period.  So later records are ignored.

The program OUTPUTs the record-in-hand prior to updating CUM_PRIOR_AMT.  That's how to delay including the current TRAN_AMT in the cumulative total.