BookmarkSubscribeRSS Feed
hamhocks
Calcite | Level 5

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!

6 REPLIES 6
Reeza
Super User

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

hamhocks
Calcite | Level 5

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

ballardw
Super User

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.

hamhocks
Calcite | Level 5

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

M

Haikuo
Onyx | Level 15

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;

hamhocks
Calcite | Level 5

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

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!

How to Concatenate Values

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.

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
  • 6 replies
  • 1293 views
  • 7 likes
  • 4 in conversation