01-23-2015 02:20 PM
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.
I am thinking the code i use for cumulative counts may work if amended somehow...
if state ne ' ';
if first.state then count=0;
count = count + 1;
01-23-2015 03:35 PM
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
01-23-2015 03:39 PM
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;
by <sort order>.
daysdif = dif(date);
if first.client /* assumes client only without the state /store etc differences*/
proc summary data=want;
output out=differencesum mean= ;
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.
01-23-2015 04:53 PM
Maybe I have mistaken your request, but could it be as simple as:
select count(*) as number_shopping, range(shipped_date)/( count(*)-1) as avg_days
group by customer;