Rolling 12 month Count across Years of Customers

[RickyS]

Novinosrin, 

Your solution works as well and I appreciate that you took some time over the weekend to look at it also nice that you included the defined period as part of the output. 

[AkshayS]

Hello Everyone,

I have a similar but slight different concern in my data. Basically I am looking to create a sequence variable based on two columns : Cust_name and Year.
There are different type of customers as below:
Customer1 was with me from 2001 to 2003
Customer2 was with me only for 2001 and 2002
Customer3 was only for one year 2001
Customer4 was with me alternate year 2003,2005,2007
Customer5 2001,2002,2005,2006,2007

I want to find maximum number of years I was able to provide service to my customers.
So, Output I am looking is:
Customer1 output as 3 (as he was 2001,2002,2003)
Customer2 as 2 (as he was 2001,2002)
Customer3 as 1 (as he was only for yr 2001)
Customer4 as 1 (alternate yr so no continuous year)
Customer5 as 3 ( he was there continuously 2(2001,2002) and 3(2005,2006,2007) so I will choose max as 3).


PFB dataset for your reference:

data abc;
input customer_id Customer_name $ 3 - 11 transaction_date :mmddyy10. ;
format transaction_date mmddyy10.;
datalines;
1 Customer1 03/31/2001
1 Customer1 03/31/2002
1 Customer1 03/31/2003
2 Customer2 03/31/2001
2 Customer2 03/31/2002
3 Customer3 03/31/2001
4 Customer4 03/31/2003
4 Customer4 03/31/2005
4 Customer4 03/31/2007
5 Customer5 03/31/2001
5 Customer5 03/31/2002
5 Customer5 03/31/2005
5 Customer5 03/31/2006
5 Customer5 03/31/2007
;
run;

proc print data=abc;
run;


Looking forward for the insights.
Thanks in advance! 🙂

[mkeintz]

I suggest you start a new topic, with a complete description of your objective, rather that "I want the same except ...". It will be far easier for us to help you.

[AkshayS]

Sure Sir. Please find the below subject line with new post. Looking forward for your recommendations. To identify the continuity and loyalty of customer over the years and find maximum continuity year. Thanks in advance!