New Contributor
Posts: 4

Calculate the Days Between Two Transactions for 4 Cohorts

I’m trying to calculate the days between two transactions for 4 cohorts.

The cohorts defined by all those which purchased on on these dates:

'28SEP2014'd, '28DEC2014'd, '29MAR2015'd, '28JUN2015'd

And it’s the difference between the last transaction before the transaction date which define the cohorts. Here is an example:

 CUSTOMER_ID transaction_date 2312544 12-Oct-14 2312544 21-Oct-14 2312544 19-Nov-14 2312544 11-Dec-14 2312544 28-Dec-14 2312544 9-Jan-15 2312544 10-Jan-15 2312544 11-Jan-15 2616131 10-Aug-08 2616131 7-Dec-08 2616131 16-Dec-12 2616131 12-Apr-13 2616131 24-Jul-13 2616131 28-Sep-14 2616131 3-Nov-14 2616131 9-Mar-15 24526667 28-Jun-15 84895398 29-Mar-15 5555666 16-Dec-12 5555666 12-Apr-13 5555666 30-Jul-13 5555666 28-Sep-14 5555666 30-Sep-14 5555666 15-Oct-14

Then I should get this.

 CUSTOMER_ID transaction_date 2312544 11-Dec-14 2312544 28-Dec-14 2616131 24-Jul-13 2616131 28-Sep-14 5555666 30-Jul-13 5555666 28-Sep-14

Noticed these two observations were dropped from above because these customers had no previous transactions

 24526667 28-Jun-15 84895398 29-Mar-15

And then the final  output should look like this:

 CUSTOMER_ID transaction_date Days Between 2312544 28-Dec-14 17 2616131 28-Sep-14 431 5555666 28-Sep-14 425

Any assistance will be greatly appreciated. Thanks!!!

New Contributor
Posts: 4

Re: Calculate the Days Between Two Transactions for 4 Cohorts

Hi. You can try this solution. Maybe not the most sophisticated but it should work.

data TestData;

infile datalines dlm=' ';

input Var1  date date9. ;

format date date9.;

datalines;

2312544 12-Oct-14

2312544 21-Oct-14

2312544 19-Nov-14

2312544 11-Dec-14

2312544 28-Dec-14

2312544 9-Jan-15

2312544 10-Jan-15

2312544 11-Jan-15

2616131 10-Aug-08

2616131 7-Dec-08

2616131 16-Dec-12

2616131 12-Apr-13

2616131 24-Jul-13

2616131 28-Sep-14

2616131 3-Nov-14

2616131 9-Mar-15

24526667 28-Jun-15

84895398 29-Mar-15

5555666 16-Dec-12

5555666 12-Apr-13

5555666 30-Jul-13

5555666 28-Sep-14

5555666 30-Sep-14

5555666 15-Oct-14

;

data testdata;

set testdata;

if date in('28SEP2014'd, '28DEC2014'd, '29MAR2015'd, '28JUN2015'd)

then flag=1;

run;

Proc Sort Data=testdata Out= test_sorted;

By var1 date ;

Run;

data Want;

set test_sorted;

by var1;

if first.var1 and last.var1 then delete; * singel observations;

lag_date=lag(date);

if flag then days=date-lag_date;

if flag;

drop flag;

run;

Super User
Posts: 9,599

Re: Calculate the Days Between Two Transactions for 4 Cohorts

Much the same really, just one datastep rather than 2.

proc sort data=testdata;

by var1 date;

run;

data want (drop=lstdate);

set testdata;

retain lstdate;

by var1;

if first.var1 then lstdate=date;

if date in ('28sep2014'd,'28dec2014'd,'29mar2015'd,'28jun2015'd) and lag(var1)=var1 then do;

days_between=date - lstdate;

output;

end;

lstdate=date;

run;

Discussion stats
• 2 replies
• 369 views
• 3 likes
• 3 in conversation