Average Reorder Cycle

[manojdawson]

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

Could someone please help me with this?

Thanks

[stat_sas · Accepted solution]

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;

[manojdawson]

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

[Haikuo]

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;