number of consecutive months from the first communication

[Vendy]

Hi all,

I wouold like to ask you for help with this problem.

I have list of customers and for each customer I have list of months in which this customer contacted us. I need to know in how many months a particular customer contacted us. But there is problem that I need to know only number of consecutive months from his first contact.

So I have table like this

    +---------+-------+
    | cust id | month |
    +---------+-------+
    |       1 | 2     |
    |       1 | 3     |
    |       1 | 4     |
    |       1 | 5     |
    |       1 | 8     |
    |       1 | 9     |
    |       1 | 10    |
    |       1 | 11    |
    |       1 | 12    |
    +---------+-------+

And I need to add column like this

    +---------+-------+-------+
    | cust id | month | flg   |
    +---------+-------+-------+
    |       1 | 2     | 1     |
    |       1 | 3     | 1     |
    |       1 | 4     | 1     |
    |       1 | 5     | 1     |
    |       1 | 8     | 0     |
    |       1 | 9     | 0     |
    |       1 | 10    | 0     |
    |       1 | 11    | 0     |
    |       1 | 12    | 0     |
    +---------+-------+-------+

So finally I only sum all 1 in column flg. The result will be that consumer 1 contacted us 4 times from his first contact consecutive.

I have tried use something like this but it does not work 😞 I do not know how to do that 1 will be only for fist consecutive line.

data test1;
     set customer_base;    
     retain month_ret;
     output;
     by cust_id month;
     month_ret = month;
run;

Data test2;
Set test1;
By cust_id;
If first.cust_id then i=1;
if month= month_ret+1 then i=1;
if month<>month_ret+1 then output;
Run;

Thank you very much

[Vendy · Accepted solution]

And here is solution 🙂

   data have;
    infile datalines;
    input cust_id month;
    datalines;
    1 2
    1 3
    1 4
    1 5
    1 8
    1 9
    1 10
    1 11
    1 12
    ;
    run;

    data want (drop=p_month);
    set have;
    by cust_id;
    retain flg p_month;
    if first.cust_id then flg=1;
    else if month ne p_month+1 then flg=0;
    p_month=month;
    run;