Hi SAS experts,  I am using SAS University Edition v.9.4.    I need your help with writing a code that will help me doing the following task:   I must undersample (or oversample) the dataset such that the observations linked to the binary variable churn (0 no churn and 1 churn) are distributed with a proportion of 80% not-churn and 20% churn. In other words, with a dataset of i.e. 100 observations I must end up with 80% observations being churn=0 and 20% being churn=1.   Right now I have a dataset that has only 0.67% of customers being churners and 99.927% of non-churners. My dataset has approximately 65790 observations and the frequency of churners is only 330 of the dataset, I must oversample or undersample (i don't know what is best) in order to have an 80-20 distribution. Unfortunately, I cannot come up with an actionable code that succeeds in the task. Below a small data step of 100 observations that represents what my real dataset is. Hopefully, you can play around with it to create the code that will allow me to achieve the desired goal.   data have; input customer_id_ano churn; cards; 1 0 2 0 3 0 4 0 5 0 6 0 7 0 8 0 9 0 10 0 11 0 12 0 13 0 14 0 15 0 16 0 17 0 18 0 19 0 20 0 21 0 22 0 23 0 24 0 25 0 26 0 27 0 28 0 29 0 30 0 31 0 32 0 33 0 34 0 35 0 36 0 37 0 38 0 39 0 40 0 41 0 42 0 43 0 44 0 45 0 46 0 47 0 48 0 49 0 50 0 51 0 52 0 53 0 54 0 55 0 56 0 57 0 58 0 59 0 60 0 61 1 62 0 63 0 64 0 65 0 66 0 67 0 68 0 69 0 70 0 71 0 72 1 73 1 74 0 75 0 76 0 77 0 78 0 79 0 80 0 81 0 82 1 83 0 84 0 85 0 86 0 87 0 88 0 89 0 90 0 91 0 92 0 93 1 94 0 95 0 96 0 97 0 98 0 99 0 100 0; run;   Thank you in advance for the precious help you always provide me with! ... View more

Hi all, I am using SAS University Edition v.9.4. and I am working on a dataset that detect when a customer is about to churn from a bank. Now, I need the following task to be performed by SAS and hoefully you can help. But first, let me provide you with a small datastep of my sample: data have; input customer_id_ano year month churn; cards; 1 2017 1 0 1 2017 2 0 1 2017 3 0 1 2017 4 0 1 2017 5 0 1 2017 6 0 1 2017 7 0 1 2017 8 0 1 2017 9 1 1 2017 10 1 1 2017 11 1 1 2017 12 1 2 2017 1 0 2 2017 2 0 2 2017 3 0 2 2017 4 1 2 2017 5 1 2 2017 6 1 2 2017 7 1 2 2017 8 1 2 2017 9 1 2 2017 10 1 2 2017 11 1 2 2017 12 1 3 2017 1 1 3 2017 2 1 3 2017 3 1 3 2017 4 1 3 2017 5 1 3 2017 6 1 3 2017 7 1 3 2017 8 1 3 2017 9 1 3 2017 10 1 3 2017 11 1 3 2017 12 1 4 2017 1 0 4 2017 2 0 4 2017 3 0 4 2017 4 1 4 2017 5 1 4 2017 6 1 4 2017 7 1 4 2017 8 1 4 2017 9 1 4 2017 10 1 4 2017 11 1 4 2017 12 1 5 2017 1 0 5 2017 2 1 5 2017 3 1 5 2017 4 1 5 2017 5 1 5 2017 6 1 5 2017 7 1 5 2017 8 1 5 2017 9 1 5 2017 10 1 5 2017 11 1 5 2017 12 1 ; run; Now, what I need is SAS to detect when the variable churn is 1 for the first time and delete all the observations following that event. In other words, instead of a list of 1s following the first churn event, I only need to keep the first 1 and delete the remaining ones.  Let´s take customer_id_ano 1 as an example. We can see from the data step above that the variable churn for this customer is 1 starting from September 2017 and it remains 1 until the end of the observation period. Well, I only want the first churn=1 that occurred in September to remain in my dataset and the following observation must be dropped from it.  I hope I managed to explai myself. And I would like to thank you in advance for the help you always provide to all the SAS beginners like me. Very appreciated!! ... View more

Hi all,  I am working on SAS University Edition v.9.4. and I need your help to change the value of a variable across time. I know it is an easy task, but I am a SAS beginner user.   I will first provide you with a small datastep of my dataset. So that you can play a bit around with it! data have; input customer_id_ano year month customer_status_numeric; cards;1 2017 1 0 1 2017 2 0 1 2017 3 0 1 2017 4 0 1 2017 5 0 1 2017 6 1 1 2017 7 1 1 2017 8 0 1 2017 9 0 1 2017 10 1 1 2017 11 1 1 2017 12 0 2 2017 1 0 2 2017 2 0 2 2017 3 0 2 2017 4 1 2 2017 5 1 2 2017 6 1 2 2017 7 1 2 2017 8 1 2 2017 9 1 2 2017 10 0 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 1 4 2017 2 1 4 2017 3 1 4 2017 4 1 4 2017 5 1 4 2017 6 1 4 2017 7 1 4 2017 8 0 4 2017 9 0 4 2017 10 0 4 2017 11 1 4 2017 12 1 5 2017 1 1 5 2017 2 1 5 2017 3 1 5 2017 4 1 5 2017 5 1 5 2017 6 1 5 2017 7 1 5 2017 8 1 5 2017 9 1 5 2017 10 1 5 2017 11 1 5 2017 12 1; run;  I need a code that can perform the following task:   - When the variable customer_status_numeric turns into 1 I would like it to be 1 across the remaining following periods. In other words, as soon as customer_id _ano 1 turns into being 1 (customer_status_numeric=1) in June 2017 (as displayed in the data step above) I want him/her to stay 1 for the rest of the following months. Thus, customer_status_numeric should not turn into 0 in August 2017, but remain 1. And the same for the rest of the customers. I need this variable customer_status_numeric not to reset into 0 again once it has changed its value into 1. I hope I´ve provided you with a clear picture of what my issue is. Thank you in advance and do not hesitate to ask further questions if you need to! ... View more

Hi all, I need some of your precious insights for my Master Thesis and hopefully you will help.   I am trying to build a churn predictive model for a retail bank and I would like to use regression analysis for doing it. In particular, I would like to use the logit to achieve my goal.   My dataset is a panel data with 135000 customers whose behavior was tracked during an observation period of T=17 months. I have 50 variables in the dataset. 22 variables are dummies which describe whether or not the client has a give financial product or account as well as whether or not the client has done any debit or credit transaction on them. Then, the rest are demographic variable plus continous and descrete variables that describe how the client's earnings and financial position and credit risk have changed over time.   Now, I have finished with the preprocessing phase. I have formatted variables, imputed missing values, delimited my dataset by age in order to eliminate the involuntary churners and created a target variable called "churn".    My doubts now are:   1) How can I detect outliers? Does the following code work also for longitudinal data?   proc reg data=have; title "ABC Outliers"; model churn=Customer_id_Ano Month family_id_Ano Earning_Cust12M Earning_Fam12M customer_status_Numeric account_basic_sum csi customer_trans_count logons meeting_n overdraft_facility_sum volume_investments volume_loans volume_pension_lifeins volume_savings LTV_current Teller_trans_3m_last customer_age first_account_open account_save_debit_transaction account_save_credit_transaction account_save account_savings_volume basic_bank_debit_transaction basic_bank_credit_transaction credit_card_debit_transaction credit_card_credit_transaction loan_home loan_home_volume meeting_with_client atm_transaction dialogue_with_advisor secure_message_sent teller_dialogue basic_banking day_to_day_finance ebank_0 home_finance insurance investments pension personal_lending savings num_adults num_kids customer_gender ; output out=want (keep= churn Customer_id_Ano Month family_id_Ano Earning_Cust12M Earning_Fam12M customer_status_Numeric account_basic_sum csi customer_trans_count logons meeting_n overdraft_facility_sum volume_investments volume_loans volume_pension_lifeins volume_savings LTV_current Teller_trans_3m_last customer_age first_account_open account_save_debit_transaction account_save_credit_transaction account_save account_savings_volume basic_bank_debit_transaction basic_bank_credit_transaction credit_card_debit_transaction credit_card_credit_transaction loan_home loan_home_volume meeting_with_client atm_transaction dialogue_with_advisor secure_message_sent teller_dialogue basic_banking day_to_day_finance ebank_0 home_finance insurance investments pension personal_lending savings num_adults num_kids customer_gender r) rstudent=r; run; quit; data new; set want; if abs(r)>2 then delete; run; proc univariate data=want plots plotsize=30; var r; run; 2) How can I check for variables multicollinearity with a longitudinal data and logistic regression? Can I use the VIF model?   3) Shall I split the dataset into Train and Validation? If yes, what is the code I can use when my dataset is a panel data? I must be sure that when SAS splits the dataset into train and validation it keeps untouched the panel data format of my dataset.    4) What procedure shall I use in SAS to run the logistic regression? I do not think thet PROC LOGISTIC is the right choice as it does not take into account the correlation between the 17 observations within a subject. Is the PROC GLIMMIX the one I might need?  I know I've addressed you many questions, but hopefully you'll be able to help. Thank you in advance! ... View more

     Hi all, I am using SAS University Edition v.9.4. I have this error coming out everytime I try to perform the following code- that is, a simple outliers detection with residuals. I know lots of you will tell me that it is due to the file size. However, I tried to run exactly the same code yesterday and it was working. What happened today?   1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 61 62 proc reg data=churn.bank_churn PLOTS(MAXPOINTS=none); 63 model churn= Address_id_Ano 64 Customer_id_Ano 65 Distance_Closest_branch 66 Distance_home_branch 67 Earning_Cust12M 68 Earning_Fam12M 69 LTV_current 70 Latitude_Ano 71 Longitude_Ano 72 Month 73 RD_CUST_YEARS 74 RD_RESTGLD 75 Teller_trans_3m_last 76 account_basic_sum 77 account_save 78 account_save_transaction 79 account_savings_volume 80 atm_transaction 81 basic_bank_attrdb 82 basic_bank_attrkr 83 basic_bank_bal_ult 84 basic_bank_crbal_avg 85 basic_bank_crbal_max 86 basic_bank_crbal_min 87 basic_bank_days_cr 88 basic_bank_days_db 89 basic_bank_dball_max 90 basic_bank_dbbal_avg 91 basic_bank_dbbal_max 92 basic_bank_dbbal_min 93 basic_bank_dbomakbas 94 basic_bank_kromakbas 95 basic_bank_utilized 96 basic_banking 97 credit_card 98 credit_card_transaction 99 csi 100 customer_age 101 customer_status_Numeric 102 customer_trans_count 103 day_to_day_finance 104 dialogue_with_advisor 105 ebank_0 106 family_id_Ano 107 first_account_open 108 home_finance 109 insurance 110 investments 111 loan_home 112 loan_home_volume 113 logons 114 meeting_n 115 meeting_with_client 116 num_adults 117 num_kids 118 overdraft_facility_sum 119 pension 120 personal_lending 121 savings 122 secure_message_sent 123 teller_dialogue 124 volume_investments 125 volume_loans 126 volume_pension_lifeins 127 volume_savings ; 128 output out=outliers.bank_outliers_r (keep= churn Address_id_Ano 129 Customer_id_Ano 130 Distance_Closest_branch 131 Distance_home_branch 132 Earning_Cust12M 133 Earning_Fam12M 134 LTV_current 135 Latitude_Ano 136 Longitude_Ano 137 Month 138 RD_CUST_YEARS 139 RD_RESTGLD 140 Teller_trans_3m_last 141 account_basic_sum 142 account_save 143 account_save_transaction 144 account_savings_volume 145 atm_transaction 146 basic_bank_attrdb 147 basic_bank_attrkr 148 basic_bank_bal_ult 149 basic_bank_crbal_avg 150 basic_bank_crbal_max 151 basic_bank_crbal_min 152 basic_bank_days_cr 153 basic_bank_days_db 154 basic_bank_dball_max 155 basic_bank_dbbal_avg 156 basic_bank_dbbal_max 157 basic_bank_dbbal_min 158 basic_bank_dbomakbas 159 basic_bank_kromakbas 160 basic_bank_utilized 161 basic_banking 162 churn 163 credit_card 164 credit_card_transaction 165 csi 166 customer_age 167 customer_status_Numeric 168 customer_trans_count 169 day_to_day_finance 170 dialogue_with_advisor 171 ebank_0 172 family_id_Ano 173 first_account_open 174 home_finance 175 insurance 176 investments 177 loan_home 178 loan_home_volume 179 logons 180 meeting_n 181 meeting_with_client 182 num_adults 183 num_kids 184 overdraft_facility_sum 185 pension 186 personal_lending 187 savings 188 secure_message_sent 189 teller_dialogue 190 volume_investments 191 volume_loans 192 volume_pension_lifeins 193 volume_savings r) rstudent=r; 194 run;   ERROR: Insufficient space in file WORK._DOCTMP000000000000000000003.DATA. ERROR: No observations have been read from data set WORK._DOCTMP000000000000000000003. ERROR: No observations have been read from data set .' WORK.SASTMP-00000001303DATA DATA3.ITEMSTOR is in use by you in resource environment __ODS_DOCMAN.`z�� @�A�����ȩ����z����z���'n. ERROR: No observations have been read from data set .' WORK.SASTMP-00000001303DATA DATA3.ITEMSTOR is in use by you in resource environment __ODS_DOCMAN.`z�� @�A�����ȩ����z����z���'n. NOTE: The SAS System stopped processing this step because of errors.   WARNING: The data set OUTLIERS.DANSKE_BANK_OUTLIERS_R may be incomplete. When this step was stopped there were 0 observations and 0 variables. WARNING: Data set OUTLIERS.DANSKE_BANK_OUTLIERS_R was not replaced because this step was stopped. NOTE: PROCEDURE REG used (Total process time): real time 1:16.39 cpu time 1:04.52   195 quit; 196 197 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 210   ... View more

Hi all,   I am using SAS University Edition v.9.4. I am imputing missing value in my panel data and I need your help. Below a sample data step: data have; input cust_id trans_max trans_min trans_avg; cards; 1 10 2 6 2 5 2 . 3 20 2 11 4 14 4 . 5 3 5 .  As you can see, the variable "trans_avg" is nothing else that the arithmetic average between the variables "trans_max" and "trans_min".   In other words "trans_avg" is equal to: trans_avg=(trans_max+trans_min)/2   How can I tell SAS to replace the missing values with the arithmetic average between trans_max and trans_min? I wanted to use a code as simple as the one shown below, but SAS cannot read my formula:     data new; set have; if trans_avg=. then trans_avg=((trans_max+trans_min)/2); run; I know this code is wrong, but I am not so good at using SAS yet. So, hopefully you can help. Thank you in advance. ... View more

Hi all, I have an issue with my dataset that I hope you will help me to solve. I am using SAS University Edition v.9.4.   In my dataset, which is a panel data sorted by the variables customer ID, year and month, I have some missing values linked to the variable customer_age that I would like to substitute with their lagged values. Below, a small data step of my dataset to play with it around.  As you will see, I have four types of missing values: 1. Clients whose age is first observed, then missing and observed again (See cust_id 1). In this case, SAS should substitute the missing value with the previous observation. 2. Client whose age is first missing and then observed (See cust_id 3). In this case, SAS should substitute the missing with the first next observation it can find. In the case of cust_id 3, SAS should substitute the missing values from January 2017 and February 2017 with the customer_age observed on March 2017. 3. Clients whose age is always missing (See cust_id 5). In this case, I would like SAS to delete these observations from the dataset. In other words, if customer_age is a missing value from January 2017 to December 2017, then delete.  I hope I've given you the idea of my issue and hopefully you will help!  Thank you in advance.   data have; input cust_id year month customer_age; cards; 1 2017 1 23 1 2017 2 23 1 2017 3 23 1 2017 4 . 1 2017 5 . 1 2017 6 . 1 2017 7 23 1 2017 8 23 1 2017 9 23 1 2017 10 23 1 2017 11 23 1 2017 12 23 2 2017 1 41 2 2017 2 41 2 2017 3 41 2 2017 4 41 2 2017 5 41 2 2017 6 41 2 2017 7 41 2 2017 8 41 2 2017 9 41 2 2017 10 41 2 2017 11 41 2 2017 12 . 3 2017 1 . 3 2017 2 . 3 2017 3 22 3 2017 4 22 3 2017 5 22 3 2017 6 22 3 2017 7 22 3 2017 8 22 3 2017 9 22 3 2017 10 22 3 2017 11 22 3 2017 12 22 4 2017 1 34 4 2017 2 34 4 2017 3 34 4 2017 4 34 4 2017 5 34 4 2017 6 34 4 2017 7 34 4 2017 8 34 4 2017 9 34 4 2017 10 34 4 2017 11 34 4 2017 12 34 5 2017 1 . 5 2017 2 . 5 2017 3 . 5 2017 4 . 5 2017 5 . 5 2017 6 . 5 2017 7 . 5 2017 8 . 5 2017 9 . 5 2017 10 . 5 2017 11 . 5 2017 12 . 6 2017 1 56 6 2017 2 . 6 2017 3 . 6 2017 4 . 6 2017 5 . 6 2017 6 56 6 2017 7 56 6 2017 8 56 6 2017 9 56 6 2017 10 56 6 2017 11 56 6 2017 12 56 7 2017 1 45 7 2017 2 45 7 2017 3 45 7 2017 4 45 7 2017 5 45 7 2017 6 45 7 2017 7 45 7 2017 8 45 7 2017 9 45 7 2017 10 45 7 2017 11 45 7 2017 12 . ; run;   ... View more