topic Re: Average Days Between Transaction Counts in SAS Programming

Average Days Between Transaction Counts

RobertNYC — Thu, 23 Jul 2015 15:03:08 GMT

Hi,

I have a data set with customer IDs and transaction dates. I need to calculate 5 variables using this data. They are.

1. Average Days Between Each Transaction

2. Average Days Between Transactions at 6 month intervals

3. Average Days Between Transactions at 12 Month intervals

4. Average Days First and Last Transaction

5. Average Total Transactions

And then I need to aggregate these all together.

And then group by year.

I start with this data:

CUSTOMER_ID	transaction_date
440767	13-Jan-07
440767	5-Jan-12
440767	3-Jun-09
440767	17-Jan-12
440767	9-Apr-09
440767	10-Aug-08
1795023	10-Jul-12
1795023	11-Aug-11
1795023	30-Nov-09
1795023	9-Jan-12
1795023	20-Jan-11
1795023	10-Apr-09
1795023	2-Nov-11
1795023	8-Feb-12
1795023	17-Aug-11
1795023	19-Nov-13
3627695	29-May-06
3627695	15-May-03
3627695	23-Mar-15
3627695	18-May-06

here are some examples (they counts don't match the sample data)

One output would look like thisAvery Days Between Transaction Counts

avr_Days_Betw_Each_trans	AvrDaysBetw_trans_sixMonth_Intervals	avrDaysBetw_trans_year_Intervals	avr_Days between_first_last_trans	Total_Trans_Count
10	30	100	1000	50

and one would look like this

Year	avr_Days_Betw_Each_trans	AvrDaysBetw_trans_sixMonth_Intervals	avrDaysBetw_trans_year_Intervals	avr_Days between_first_last_trans	Total_Trans_Count
2012	10	30	100	1000	50
2013	30	60	200	555	85
2014	40	70	300	456	90

Any assistance would be appreciated. Thanks

Re: Average Days Between Transaction Counts

Steelers_In_DC — Thu, 23 Jul 2015 15:31:36 GMT

This should be a pretty good start.

data have;

infile cards dsd;

informat customer_id $7. transaction_date date8.;

format customer_id $7. transaction_date date8.;

input customer_id$ transaction_date;

cards;

440767,13-Jan-07

440767,5-Jan-12

440767,3-Jun-09

440767,17-Jan-12

440767,9-Apr-09

440767,10-Aug-08

1795023,10-Jul-12

1795023,11-Aug-11

1795023,30-Nov-09

1795023,9-Jan-12

1795023,20-Jan-11

1795023,10-Apr-09

1795023,2-Nov-11

1795023,8-Feb-12

1795023,17-Aug-11

1795023,19-Nov-13

3627695,29-May-06

3627695,15-May-03

3627695,23-Mar-15

3627695,18-May-06

;

proc sort data= have;by customer_id transaction_date;

data prep;

set have;

format l_transaction_date date8.;

retain first_transaction;

by customer_id transaction_date;

l_transaction_date = lag(transaction_date);

if not first.customer_id then do;

date_diff = transaction_date-l_transaction_date;

end;

if first.customer_id then first_transaction = transaction_date;

if last.customer_id then last_transaction = transaction_date;

if last.customer_id then first_last_diff = last_transaction-first_transaction;

run;

proc sql;

create table prep2 as

select distinct customer_id,transaction_date,date_diff,first_last_diff,avg(date_diff) as avg_days

from prep

group by customer_id;

Re: Average Days Between Transaction Counts

RobertNYC — Thu, 23 Jul 2015 15:51:29 GMT

Thanks this, its helpful help. I'm still not sure how to do by 6 month intervals and and by 12 month intervals and do a transaction count.

Re: Average Days Between Transaction Counts

Vish33 — Thu, 23 Jul 2015 16:21:04 GMT

Try this. Need to do some modifications at your end.

data have;
input CUSTOMER_ID transaction_date date9.;
format transaction_date date9.;
cards;
440767 13-Jan-07
440767 5-Jan-12
440767 3-Jun-09
440767 17-Jan-12
440767 9-Apr-09
440767 10-Aug-08
1795023 10-Jul-12
1795023 11-Aug-11
1795023 30-Nov-09
1795023 9-Jan-12
1795023 20-Jan-11
1795023 10-Apr-09
1795023 2-Nov-11
1795023 8-Feb-12
1795023 17-Aug-11
1795023 19-Nov-13
3627695 29-May-06
3627695 15-May-03
3627695 23-Mar-15
3627695 18-May-06
;
run;

proc sort data=have;
by CUSTOMER_ID transaction_date;
run;

data want;
set have;
by customer_id ;
retain transaction_date;
dt=lag(transaction_date);
avr_Days_Betw_Each_trans=mean(intck('day',transaction_date,dt));
AvrDaysBetw_trans_sixMonth=mean(intck('QTR2',transaction_date,dt));
avrDaysBetw_trans_year_Intervals=mean(intck('year',transaction_date,dt));
if first.customer_id then ftran=transaction_date;
if last.customer_id then ltran=transaction_date;
avr_Days_first_last_trans=mean(intck('day',ftran,ltran));
format dt ftran ltran date9.;
drop ftran ltran dt;
run;

proc sql;
select avg(avr_Days_Betw_Each_trans) as avr_Days_Betw_Each_trans,
avg(AvrDaysBetw_trans_sixMonth) as AvrDaysBetw_trans_sixMonth,
avg(avrDaysBetw_trans_year_Intervals) as avrDaysBetw_trans_year_Intervals,
avg(avr_Days_first_last_trans) asavr_Days_first_last_trans
from want;
quit;

Rgds,

Vish

Re: Average Days Between Transaction Counts

Steelers_In_DC — Thu, 23 Jul 2015 16:35:59 GMT

You have to explain what you mean with 6 and 12 month intervals. I added an intck() to get the month difference and fields to get the difference but your explanation isn't very thorough about what you mean. Take a look at the month6_diff and month12_diff fields.

data have;

infile cards dsd;

informat customer_id $7. transaction_date date8.;

format customer_id $7. transaction_date date8.;

input customer_id$ transaction_date;

cards;

440767,13-Jan-07

440767,5-Jan-12

440767,3-Jun-09

440767,17-Jan-12

440767,9-Apr-09

440767,10-Aug-08

1795023,10-Jul-12

1795023,11-Aug-11

1795023,30-Nov-09

1795023,9-Jan-12

1795023,20-Jan-11

1795023,10-Apr-09

1795023,2-Nov-11

1795023,8-Feb-12

1795023,17-Aug-11

1795023,19-Nov-13

3627695,29-May-06

3627695,15-May-03

3627695,23-Mar-15

3627695,18-May-06

;

proc sort data= have;by customer_id transaction_date;

data prep;

set have;

format l_transaction_date date8.;

retain first_transaction;

by customer_id transaction_date;

l_transaction_date = lag(transaction_date);

if not first.customer_id then do;

date_diff = transaction_date-l_transaction_date;

month_diff = intck('month',l_transaction_date,transaction_date);

end;

if first.customer_id then first_transaction = transaction_date;

if last.customer_id then last_transaction = transaction_date;

if last.customer_id then first_last_diff = last_transaction-first_transaction;

if month_diff >= 6 then do;

month6_diff = transaction_date-l_transaction_date;

end;

if month_diff >= 12 then do;

month12_diff = transaction_date-l_transaction_date;

end;

run;

proc sql;

create table want as

select distinct customer_id,transaction_date,date_diff,first_last_diff,avg(date_diff) as avg_days,month_diff,month6_diff,month12_diff

from prep

group by customer_id;

Re: Average Days Between Transaction Counts

RobertNYC — Thu, 23 Jul 2015 16:48:55 GMT

Yes sorry I should have been more specific.

So in a 6 month period (intervals) – So I guess you would have to say what are the average days between purchases during every 182.5 days and for year 365 days.

Re: Average Days Between Transaction Counts

Steelers_In_DC — Thu, 23 Jul 2015 16:57:55 GMT

data prep;

set have;

format l_transaction_date date8.;

retain first_transaction;

by customer_id transaction_date;

l_transaction_date = lag(transaction_date);

if not first.customer_id then do;

date_diff = transaction_date-l_transaction_date;

end;

if first.customer_id then first_transaction = transaction_date;

if last.customer_id then last_transaction = transaction_date;

if last.customer_id then first_last_diff = last_transaction-first_transaction;

if 182.5 <= date_diff <= 365 then month6_diff = date_diff;

if date_diff > 365 then month12_diff = date_diff;

year = year(transaction_date);

run;

proc sql;

create table want as

select distinct customer_id,transaction_date,date_diff,first_last_diff,avg(date_diff) as avg_days,avg(month6_diff) as avg_6month,avg(month12_diff) as avg_12month

from prep

group by customer_id;

Re: Average Days Between Transaction Counts

RobertNYC — Thu, 23 Jul 2015 17:14:22 GMT

I'm not sure thats right. Its the average days between purchases every during a 182.5 window

First 182.5 days: I had 3 purchases and the average time between them was 27 days

Second 182.5 days: I had 5 purchases and then the average time between them was 15 days

Third 182.5 days: I had 2 purchases and then the average time between them was 30 days

So 6 moth average is 24.

Does that make more sense?

Re: Average Days Between Transaction Counts

RobertNYC — Thu, 23 Jul 2015 18:19:40 GMT

Hi Vish33,

I'm just wondering why you used the 'QTR' option to calculate the 6 momth averages.

As I mentioned above, Its the average days between purchases every during a 182.5 window

First 182.5 days: I had 3 purchases and the average time between them was 27 days

Second 182.5 days: I had 5 purchases and then the average time between them was 15 days

Third 182.5 days: I had 2 purchases and then the average time between them was 30 days

So 6 moth average is 24.

Re: Average Days Between Transaction Counts

ndp — Thu, 23 Jul 2015 20:09:12 GMT

do you need this for each customer? or just each year?

Re: Average Days Between Transaction Counts

RobertNYC — Thu, 23 Jul 2015 20:19:17 GMT

Hi Aggregated by each year and then all years aggregated. Thanks !

Re: Average Days Between Transaction Counts

ndp — Fri, 24 Jul 2015 14:27:03 GMT

One more question day 1= first transaction date or day 1= 1 Jan of first transcation year?

Re: Average Days Between Transaction Counts

RobertNYC — Fri, 24 Jul 2015 14:32:58 GMT

Hi ndp,

First transaction date or day. Thanks!!!

Re: Average Days Between Transaction Counts

ndp — Fri, 24 Jul 2015 14:52:46 GMT

lets asay we have transaction dates dt. Then does the table below make sense esspecially for mon6/12 vars?

dt _dt day mon6 mon12

13JAN07 13JAN07 1 1 1

10AUG08 13JAN07 576 4 2

09APR09 10AUG08 243 2 1

03JUN09 09APR09 56 1 1

05JAN12 03JUN09 947 6 3

17JAN12 05JAN12 13 1 1

Re: Average Days Between Transaction Counts

RobertNYC — Fri, 24 Jul 2015 15:08:38 GMT

Yes it does!!!

Re: Average Days Between Transaction Counts

ndp — Fri, 24 Jul 2015 15:28:52 GMT

wonderful. try this.

data have;
set have;
dt=input(dtc,date7.);
format dt date7.;
year=year(dt);
run;

proc sort data= have;by dt;
run;

data have1;
set have;
by dt;
retain _dt;
_dt=lag(dt);
if _n_=1 then _dt=dt;
day=dt-_dt+1;
mon6=ceil(day/182.5);
mon12=ceil(day/365);
format _dt date7.;
drop id year dtc;
run;

*** overall avg ;
proc sql noprint;
create table avg1 as
select mean(day) as avg1
from have1;
quit;

*** 6 mon avg;
proc sql noprint;
create table _avg6 as
select mon6, mean(day) as _avg6
from have1
group by mon6;
quit;

proc sql noprint;
create table avg6 as
select mean(_avg6) as avg6
from _avg6
;
quit;

*** 12 mon avg;
proc sql noprint;
create table _avg12 as
select mon6, mean(day) as _avg12
from have1
group by mon12;
quit;

proc sql noprint;
create table avg12 as
select mean(_avg12) as avg12
from _avg12
;
quit;

if this works you can add var 'year' in by statements starting with proc sort and instead of 'if _n_=1....' use 'if first.year....'