Write and run SAS programs in your web browser

How to create a binary variable using lag function and counter

Reply
Occasional Contributor
Posts: 5

How to create a binary variable using lag function and counter

[ Edited ]

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; 

 

Super User
Posts: 7,781

Re: How to create a binary variable using lag function and counter

Posted in reply to Alessandra_005

We had that same issue in https://communities.sas.com/t5/SAS-Studio/How-to-create-a-dichotomous-variable-on-SAS-University-Edi....

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: How to create a binary variable using lag function and counter

Posted in reply to KurtBremser

Hi @KurtBremser,

 

I know it. My Thesis peer posted it. However, we have a slight edit that we should make to the code you provided us with. She tried to tag you to the old post, but apparently you have not visualized it. 

 

Thank you in advance

Super User
Posts: 7,781

Re: How to create a binary variable using lag function and counter

Posted in reply to Alessandra_005

Alessandra_005 wrote:

Hi @KurtBremser,

 

I know it. My Thesis peer posted it. However, we have a slight edit that we should make to the code you provided us with. She tried to tag you to the old post, but apparently you have not visualized it. 

 

Thank you in advance


Suggestion provided in the original thread.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,789

Re: How to create a binary variable using lag function and counter

Posted in reply to Alessandra_005

Do you have SAS accessible from another source? SAS UE doesn't support PROC EXPAND or TIMESERIES which would make this a trivial problem. 

Super Contributor
Posts: 439

Re: How to create a binary variable using lag function and counter

I believe SAS On Demand for Academics supports both those Procs though - if he hasn't done so already the OP could sign up for that as an independent learner

Ask a Question
Discussion stats
  • 5 replies
  • 167 views
  • 1 like
  • 4 in conversation