Thank you so much Patrick! It is working for all different set of combinations, which I tried more for testing. ... View more

Another approach, But facing same issue with customer type 5     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 sql; create table t1 as select Customer_name,year(transaction_date) as year from abc; quit; proc sql; create table t2 as select Customer_name,year from t1; quit;   data t3; retain customer_name customer year lyear out; set t2; keep retain customer_name customer year lyear out; customer=lag(customer_name); lyear=lag(year); out=(customer_name=customer) and (year ne (lyear+1)) ;   data t4; set t3; by Customer_name ; if first.Customer_name then SeqN=1; else SeqN + 1; drop Customer ;   proc sql; create table t5 as select Customer_name,year,SeqN,out, case when out=0 then SeqN else out end as sequence from t4; quit; proc print; run; ... View more

Hi there,   Please find the below approach taken to deal with all the types of customer behaviour in my data.   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 sql; create table t1 as select Customer_name,year(transaction_date) as year from abc; quit; proc sql; create table t2 as select Customer_name,year from t1; quit;   data t3; retain customer_name customer year lyear out; set t2; keep retain customer_name customer year lyear out; customer=lag(customer_name); lyear=lag(year); out=(customer_name=customer) and (year ne (lyear+1)) ; proc sql; create table t4 as select *, case when out=0 and customer_name=customer then 1 else 0 end as sequence from t3; quit;   data t5; set t4; by Customer_name ; retain sequence1 0 ; if first.Customer_name then sequence1=1; else sequence1=sequence; run; data t6; set t5; by Customer_name ; retain sequence2 0 ; if first.Customer_name then sequence2=sequence1; else sequence2+sequence1; run; proc sql; create table t7 as select Customer_name,year, case when out=1 and customer_name=customer and year>lyear+1 then 1 else sequence2 end as sequence3 from t6; quit; proc print; run;   Output :  customer_name year sequence3 Customer1 2001 1 Customer1 2002 2 Customer1 2003 3 Customer2 2001 1 Customer2 2002 2 Customer3 2001 1 Customer4 2003 1 Customer4 2005 1 Customer4 2007 1 Customer5 2001 1 Customer5 2002 2 Customer5 2005 1 Customer5 2006 3 Customer5 2007 4   The customer behaviour is that he is getting service for 2001,2002 and the he disappears for 2yrs and comes back in 2005 onwards. So the sequence creation is getting disturbed.   customer_name year sequence3 Customer5 2001 1 Customer5 2002 2 Customer5 2005 1 Customer5 2006 3 Customer5 2007 4   If you help me in fixing the sequence of such customer, my issue will be solved.   Thanks for your guidance. Please correct me if I am wrong at any step.   ... View more

Thanks for responding, but i have follwed same steps but it is not helping as there is different behaviour of customer as shared in example. ... View more