Desktop productivity for business analysts and programmers

number of consecutive months from the first communication

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

number of consecutive months from the first communication


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 Smiley Sad 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


Accepted Solutions
Solution
‎08-22-2017 05:55 AM
Occasional Contributor
Posts: 15

Re: number of consecutive months from the first communication

And here is solution Smiley Happy

 

   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;

 

View solution in original post


All Replies
Solution
‎08-22-2017 05:55 AM
Occasional Contributor
Posts: 15

Re: number of consecutive months from the first communication

And here is solution Smiley Happy

 

   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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 153 views
  • 0 likes
  • 1 in conversation