## Calculating Average Difference between values.

Occasional Contributor
Posts: 10

# Calculating Average Difference between values.

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!

Super User
Posts: 23,663

## Re: Calculating Average Difference between values.

Depends on what your data looks like. Without that it's hard to say.

Occasional Contributor
Posts: 10

## Re: Calculating Average Difference between values.

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

Super User
Posts: 13,500

## Re: Calculating Average Difference between values.

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.

Occasional Contributor
Posts: 10

## Re: Calculating Average Difference between values.

Ahh, i didn;t know there was a DIF function.  Very helpful! Thank you!

M

Posts: 3,167

## Re: Calculating Average Difference between values.

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;

Occasional Contributor
Posts: 10

## Re: Calculating Average Difference between values.

in my head the math wouldn't work out for that... but.. it does! and is a very simple way of going it.  Thanks!

Discussion stats
• 6 replies
• 384 views
• 7 likes
• 4 in conversation