I have a big file of sales data by client, order, store, etc.
I am trying to calculate not only how often they have shopped in an 18 month period, but the average days between purchases.
Any ideas?
I am thinking the code i use for cumulative counts may work if amended somehow...
data cumulative2013;
set top30bystatenodupA;
by state;
if state ne ' ';
retain count;
if first.state then count=0;
count = count + 1;
run;
thanks!
Depends on what your data looks like. Without that it's hard to say.
something like this:
customer # Order# shipped_date sales_qty
1 10 1/1/2014 5
1 22 3/4/2014 10
1 52 3/31/2014 10
2 199 2/14/2014 50
2 300 8/14/2014 100
3 23 3/5/2014 8
By client regardless of store or state or other factor?
Do you have the date of purchase as a SAS date variable?
If my data were one record per client store state order with a date of purchase I would approach this by:
Sort the data by how I needed group. For instance if client regardless of store and state then sort by client and date.
If the purchases needed to be within the same state, by client state date.
Then with that set;
data want;
set sorted;
by <sort order>.
daysdif = dif(date);
if first.client /* assumes client only without the state /store etc differences*/
then daysdiff=.
run;
proc summary data=want;
class client;
var daysdif;
output out=differencesum mean= ;
run;
This approach will give you one line of summary that is the average between all client days difference as well as the per client.
different sorts and class in proc summary could give you client within state and/or store or other group variable as well. The _type_ variable in the output data set indicates different combinations of the class variables and is very useful.
You can also ask for different statistics.
Ahh, i didn;t know there was a DIF function. Very helpful! Thank you!
M
Maybe I have mistaken your request, but could it be as simple as:
proc sql;
select count(*) as number_shopping, range(shipped_date)/( count(*)-1) as avg_days
from have
group by customer;
quit;
in my head the math wouldn't work out for that... but.. it does! and is a very simple way of going it. Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.