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! 🙂