Hi RickyS
From your examples (expected count of 14 in first period) I assume that the expected output is the number of sales transactions in a rolling 12 month period, regardless of who the customer was. So I am corious about your answer to mkeintz:
"If you have an id in which the first and last records do not span 12 months, should that id be ignored? Yes"
Do you really want to exculde customers from counting, if they have not been customers for at least a year, regardless of how many purchases they made? - this would also exclude any new customers from counting, until they had been on your customer list for at least a year?
I spent my evening writing a program that works according to the following rules:
- Wanted output is number of customer transactions in rolling 12 month periods.
- A period has a time window covering 12 months.
- First period is starting with the month of first transaction in input data and covers the following 11 months.
- Last period is ending with the month of last transaction in input data and covers the preceding 11 months.
- All transactions count as one in all periods where the transaction month fits into the period window.
I woke up this morning to find that mkeintz had contributed with a program, that basically does the same, but I post it because it actually makes the output I think you want, and you might find it useful in your actual coding, as it might be a little easier to modify to suit your needs. The number of periods is determined from the above mentioned rules applied to actual input data, so from the sample data, you only get 13 and not 248 periods.
data abc;
input customer_id 1. @3 Customer_name $9. @13 transaction_date mmddyy10. @24 customer_product_id 6.;
format transaction_date mmddyy10.;
cards;
1 Customer1 01/28/2001 123456
1 Customer1 02/01/2001 123457
1 Customer1 02/18/2001 123458
1 Customer1 02/19/2001 123459
1 Customer1 02/21/2001 123460
1 Customer1 02/26/2001 123461
1 Customer1 03/02/2001 123462
1 Customer1 04/01/2001 123463
1 Customer1 05/15/2001 123464
1 Customer1 06/01/2001 123465
1 Customer1 06/23/2001 123466
1 Customer1 06/26/2001 123467
1 Customer1 07/01/2001 123468
1 Customer1 07/18/2001 123469
2 Customer2 01/01/2002 123470
2 Customer2 01/28/2002 123471
2 Customer2 02/18/2002 123472
2 Customer2 02/19/2002 123473
2 Customer2 02/26/2002 123474
2 Customer2 03/02/2002 123475
2 Customer2 05/11/2002 123476
2 Customer2 05/15/2002 123477
2 Customer2 06/23/2002 123478
2 Customer2 06/30/2002 123479
2 Customer2 07/01/2002 123480
2 Customer2 07/16/2002 123481
2 Customer2 08/01/2002 123482
2 Customer2 12/31/2002 123483
;
run;
/* Get values to use in computation in data step */
/* mstart is day one in month of first transaction_date + 11. */
/* mcount is count of months from mstart to month of last transaction_date */
proc sql noprint;
select
intnx('month',min(transaction_date),11),
intck('month',intnx('month',min(transaction_date),11),
intnx('month',max(transaction_date),1))
into :mstart, :mcount
from abc;
quit;
%put %sysfunc(putn(&mstart,mmddyy10.)) &mcount;
data want; set abc end=eof;
length Periods $4 Count 8.;
Keep Periods Count;
* Array to hold counters - initiate with 0 and retain for summation;
array transcount 8 m1 - m%eval(&mcount) _temporary_;
retain m1 - m%eval(&mcount) 0;
* Offset (month of transaction_date relative to mstart);
startoffset = intck('month',&mstart,transaction_date);
* Loop over 12 months from month of transaction_date;
* Add count only if count_month is within defined interval;
do i = 1 to 12;
interval = startoffset + i;
if 0 < interval <= &mcount then do;
transcount{interval} = transcount{interval} + 1;
end;
end;
* Transpose array and output when finished;
if eof then do i = 1 to &mcount;
Periods = 'M'||left(put(i,3.));
Count = transcount{i};
output;
end;
run;
I hope this will help you. Greetings from Denmark / Erik
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 webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
4307 views
-
3 likes
-
-
