## Average Reorder Cycle

Solved
Occasional Contributor
Posts: 11

# Average Reorder Cycle

Hi

I am trying to find the average reorder cycle per customer. Currently my data set looks like

 CustCode OrderNumber Order Date 2320 549808 18Jun2001 2320 579063 31Aug2001 2320 596750 24Oct2001 2320 626494 15Jan2002 2320 663091 19Apr2002 2320 705083 30Jul2002 2320 726240 19Sep2002 2320 832005 04Jun2003 2320 907268 23Dec2003 2421 579363 03Sep2001 2421 633612 05Feb2002 2421 806055 07Apr2003 2421 929144 17Feb2004

Essentially the output I am looking for is

 CustCode OrderNumber Order Date Days between Order 2320 549808 18Jun2001 - 2320 579063 31Aug2001 73 2320 596750 24Oct2001 54 2320 626494 15Jan2002 81 2320 663091 19Apr2002 94 2320 705083 30Jul2002 101 2320 726240 19Sep2002 49 2320 832005 04Jun2003 255 2320 907268 23Dec2003 199 2421 579363 03Sep2001 - 2421 633612 05Feb2002 152 2421 806055 07Apr2003 422 2421 929144 17Feb2004 310

The final Output should look like

CustCode     Avg Reorder Days

2320               113.25

2421               294.66

Thanks

Accepted Solutions
Solution
‎10-26-2014 08:19 PM
Posts: 1,270

## Re: Average Reorder Cycle

proc sort data=have;
by CustCode OrderDate;
run;

data want(drop=sumdays avg_reorder_days cnt) final(keep=custcode avg_reorder_days where=(avg_reorder_days ne .));
set have;
by CustCode OrderDate;
days_between_order=intck('day',lag(orderdate),orderdate);
sumdays+days_between_order;
cnt+1;
if first.custcode then do;
days_between_order=.;
sumdays=0;
cnt=0;
end;
if last.custcode then avg_reorder_days=sumdays/cnt;
run;

proc print data=want;
proc print data=final;
run;

All Replies
Solution
‎10-26-2014 08:19 PM
Posts: 1,270

## Re: Average Reorder Cycle

proc sort data=have;
by CustCode OrderDate;
run;

data want(drop=sumdays avg_reorder_days cnt) final(keep=custcode avg_reorder_days where=(avg_reorder_days ne .));
set have;
by CustCode OrderDate;
days_between_order=intck('day',lag(orderdate),orderdate);
sumdays+days_between_order;
cnt+1;
if first.custcode then do;
days_between_order=.;
sumdays=0;
cnt=0;
end;
if last.custcode then avg_reorder_days=sumdays/cnt;
run;

proc print data=want;
proc print data=final;
run;

Occasional Contributor
Posts: 11

## Re: Average Reorder Cycle

Thank you Stat@sas...Works perfectly !!!!

Posts: 3,186

## Re: Average Reorder Cycle

Shouldn't it as straightforward as to use Proc SQL (not tested):

proc sql;

select custcode, (max(order_date)-min(order_date))/(count(custcode)-1) as Ave_reorder

from have

group by custcode

;

quit;

🔒 This topic is solved and locked.