Desktop productivity for business analysts and programmers

Average Reorder Cycle

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Average Reorder Cycle

Hi

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

CustCodeOrderNumberOrder Date
232054980818Jun2001
232057906331Aug2001
232059675024Oct2001
232062649415Jan2002
232066309119Apr2002
232070508330Jul2002
232072624019Sep2002
232083200504Jun2003
232090726823Dec2003
242157936303Sep2001
242163361205Feb2002
242180605507Apr2003
242192914417Feb2004

Essentially the output I am looking for is

CustCodeOrderNumberOrder DateDays between Order
232054980818Jun2001-
232057906331Aug200173
232059675024Oct200154
232062649415Jan200281
232066309119Apr200294
232070508330Jul2002101
232072624019Sep200249
232083200504Jun2003255
232090726823Dec2003199
242157936303Sep2001-
242163361205Feb2002152
242180605507Apr2003422
242192914417Feb2004310

The final Output should look like

CustCode     Avg Reorder Days

2320               113.25

2421               294.66

           

Could someone please help me with this?

Thanks


Accepted Solutions
Solution
‎10-26-2014 08:19 PM
Trusted Advisor
Posts: 1,203

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;

View solution in original post


All Replies
Solution
‎10-26-2014 08:19 PM
Trusted Advisor
Posts: 1,203

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 !!!!

Respected Advisor
Posts: 3,124

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 307 views
  • 0 likes
  • 3 in conversation