BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
manojdawson
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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

3 REPLIES 3
stat_sas
Ammonite | Level 13

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
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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