topic Re: Calculating Average Difference between values. in SAS Programming

Calculating Average Difference between values.

hamhocks — Fri, 23 Jan 2015 19:20:12 GMT

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!

Re: Calculating Average Difference between values.

Reeza — Fri, 23 Jan 2015 20:00:50 GMT

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

Re: Calculating Average Difference between values.

hamhocks — Fri, 23 Jan 2015 20:35:49 GMT

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

Re: Calculating Average Difference between values.

ballardw — Fri, 23 Jan 2015 20:39:22 GMT

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.

Re: Calculating Average Difference between values.

hamhocks — Fri, 23 Jan 2015 21:42:16 GMT

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

Re: Calculating Average Difference between values.

Haikuo — Fri, 23 Jan 2015 21:53:41 GMT

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;

Re: Calculating Average Difference between values.

hamhocks — Fri, 23 Jan 2015 23:33:51 GMT

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