Hi all,
I am using SAS University edition v9.4. I want to build a predictive model using logistic regression that can forecast the probability to churn customer have. So dependent variable is 1 if Churn and 0 if Not Churn.
Now, I have to build a logistic regression having as dependent variable the dichotomous variable 1 (churn) and 0 ( No Churn). I have to build it according to this definition:
"We define that the customer who did not do any transaction across all of his accounts and product areas on his own initiative during the last three months is a churner"
My problem is: How can I tell to SAS that if variable X is 0 for three following months- i.e. Jan, Feb,Mar- then my variable "Churn" must be 1, otherwise 0? In other words, if customer Z has been active for example from January to June then my dichotomous variable must be 0 during those months. However, if from July to September this customer made no transaction at all then from JULY my dichotomous variable must be 1. And it must be one until the end of the observation period.
Please note that I have a variable called "trans_numb" that tells me how many transaction the customer has done across all of his accounts and products areas during a specific month. So, I will use this variable as a benchmark for then constructing my dichotomous variable.
data bank;
input cust_id year month trans_numb;
cards;
1 2017 1 123
1 2017 2 456
1 2017 3 76
1 2017 4 0
1 2017 5 0
1 2017 6 0
1 2017 7 0
1 2017 8 0
1 2017 9 0
1 2017 10 0
1 2017 11 0
1 2017 12 0
2 2017 1 23
2 2017 2 15
2 2017 3 9
2 2017 4 1
2 2017 5 0
2 2017 6 0
2 2017 7 0
2 2017 8 0
2 2017 9 0
2 2017 10 1
2 2017 11 0
2 2017 12 0
3 2017 1 0
3 2017 2 0
3 2017 3 0
3 2017 4 0
3 2017 5 0
3 2017 6 0
3 2017 7 0
3 2017 8 0
3 2017 9 0
3 2017 10 0
3 2017 11 0
3 2017 12 0
4 2017 1 123
4 2017 2 456
4 2017 3 76
4 2017 4 65
4 2017 5 88
4 2017 6 90
4 2017 7 78
4 2017 8 76
4 2017 9 65
4 2017 10 89
4 2017 11 86
4 2017 12 79
5 2017 1 345
5 2017 2 456
5 2017 3 125
5 2017 4 65
5 2017 5 78
5 2017 6 100
5 2017 7 76
5 2017 8 78
5 2017 9 98
5 2017 10 99
5 2017 11 65
5 2017 12 45
6 2017 1 0
6 2017 2 0
6 2017 3 2
6 2017 4 0
6 2017 5 0
6 2017 6 0
6 2017 7 1
6 2017 8 0
6 2017 9 0
6 2017 10 0
6 2017 11 0
6 2017 12 0
7 2017 1 45
7 2017 2 36
7 2017 3 10
7 2017 4 7
7 2017 5 0
7 2017 6 0
7 2017 7 0
7 2017 8 0
7 2017 9 0
7 2017 10 0
7 2017 11 0
7 2017 12 0
;
run;
As you can see, I have four types of customers:
1- Customers that simply remain active for the whole period (see cust_id 4). In this case, the new variable churn should be 0 for the whole observation period
2- Customers that first are active and then stop doing transactions until the end of the observation period (see cust_id 7). In this case, the new variable churn should first be 0, then it should turn into 1 starting from May. In other words, SAS should recognize that the variable churn should be 1 if and only if trans_numb is 0 for three months in a row and the value 1 should start from the first of these three months onward.
3- Customers that do not do any transactions for the whole observation period (See cust_id 3). In this case, the variable churn should be 1 after the three-month pause.
4- Customers that are first active then take a three-month pause and then do 1 or 2 transactions on a random month and then return inactive (See cust_id 2). In this case if you can give me two options. 1st option--> the variable churn remains 1; 2nd option--> the variable churn reset and return being 1 again after the second three-month pause.
I hope I've given you an idea you can play around with.
If you have any further questions do not hesitate to ask!
Thank you a lot in advance!!!
p.s. below a small example data step of how I would mi final variable churn look like:
data have;
input cust_id year month trans_numb churn;
cards;
1 2017 1 123 0
1 2017 2 456 0
1 2017 3 76 0
1 2017 4 0 1
1 2017 5 0 1
1 2017 6 0 1
1 2017 7 0 1
1 2017 8 0 1
1 2017 9 0 1
1 2017 10 0 1
1 2017 11 0 1
1 2017 12 0 1
2 2017 1 23
2 2017 2 15
2 2017 3 9
2 2017 4 1
2 2017 5 0 1
2 2017 6 0 1
2 2017 7 0 1
2 2017 8 0 1
2 2017 9 0 1
2 2017 10 1 0
2 2017 11 0 0
2 2017 12 0 0
3 2017 1 2 0
3 2017 2 0 0
3 2017 3 0 0
3 2017 4 1 0
3 2017 5 0 1
3 2017 6 0 1
3 2017 7 0 1
3 2017 8 0 1
3 2017 9 0 1
3 2017 10 2 0
3 2017 11 0 0
3 2017 12 0 0
4 2017 1 123 0
4 2017 2 456 0
4 2017 3 76 0
4 2017 4 65 0
4 2017 5 88 0
4 2017 6 90 0
4 2017 7 78 0
4 2017 8 76 0
4 2017 9 65 0
4 2017 10 0 0
4 2017 11 85 0
4 2017 12 79 0
5 2017 1 345 0
5 2017 2 456 0
5 2017 3 125 0
5 2017 4 65 0
5 2017 5 78 0
5 2017 6 100 0
5 2017 7 76 0
5 2017 8 32 0
5 2017 9 15 0
5 2017 10 0 1
5 2017 11 0 1
5 2017 12 0 1
;
run;
Click image to register for webinar
