Hi Experts, I have to create complex (for me) counter variables in a dataset. I am trying to explain as clearly as possible. If anything unclear please let me know. Hope with your help I can achieve what I am expecting. I need to create three variables: Probation_Count, Probation_Flag and Cure_Count. creating of three variables are CID specific (we are grouping by CID). Probation_Count and Probation_Flag conditions Condition 1 - probation_count starts from 1 when a contract goes from Default_Flag =Y to Default_Flag = N, and probation_flag = Y. Condition 2 - Probation_count will increment as long as DPD = 0 and Default_Flag =N, probation flag =Y Condition 3 - when DPD >0 and DPD <= 3 and Defult_Flag=N, probation_count has to stay at the value when DPD = 0; probation_count will start to increase once DPD =0 and default_flag = N, probation_flag =Y, Condition 4 - when DPD >3 and default_flag = N then Probation count resets to 0 unti DPD = 0 and default_Flag=N, probation flag = Y Condition 5 - probation_count can increase until 10, and then resets to 0, probation_flag = Y until probation count =10 Condition 6 - When ever Default_Flag = Y then probation_count = 0 and Probation_flag = N. In order to start the probation_count contract has to move from default_flag= Y to Default_flag=N. Cure_count conditions Condition 1 - cure_count starts from 1 when previous date probation_count was 10 and current date default_flag = N Condition 2 - Cure_count will increase until default flag = Y or Cure_count = 10 Please find the sample data below. I have manually calculated probation_count, probation_flag and cure_count. CID Date DPD Default_Flag Probation_count Probation_Flag Cure_count 111 04/04/2021 87 N 0 0 111 05/04/2021 88 N 0 0 111 06/04/2021 89 N 0 0 111 07/04/2021 90 Y 0 0 111 08/04/2021 91 Y 0 0 111 09/04/2021 92 Y 0 0 111 10/04/2021 93 Y 0 0 111 11/04/2021 0 N 1 Y 0 111 12/04/2021 0 N 2 Y 0 111 13/04/2021 0 N 3 Y 0 111 14/04/2021 0 N 4 Y 0 111 15/04/2021 0 N 5 Y 0 111 16/04/2021 1 N 5 Y 0 111 17/04/2021 2 N 5 Y 0 111 18/04/2021 0 N 6 Y 0 111 19/04/2021 0 N 7 Y 0 111 20/04/2021 0 N 8 Y 0 111 21/04/2021 0 N 9 Y 0 111 22/04/2021 0 N 10 Y 0 111 23/04/2021 0 N 0 1 111 24/04/2021 0 N 0 2 111 25/04/2021 0 N 0 3 222 04/04/2021 86 N 0 0 222 05/04/2021 87 N 0 0 222 06/04/2021 88 N 0 0 222 07/04/2021 89 N 0 0 222 08/04/2021 90 Y 0 0 222 09/04/2021 91 Y 0 0 222 10/04/2021 92 Y 0 0 222 11/04/2021 93 Y 0 0 222 12/04/2021 94 Y 0 0 222 13/04/2021 95 Y 0 0 222 14/04/2021 96 Y 0 0 333 04/04/2021 87 N 0 0 333 05/04/2021 88 N 0 0 333 06/04/2021 89 N 0 0 333 07/04/2021 90 Y 0 0 333 08/04/2021 91 Y 0 0 333 09/04/2021 92 Y 0 0 333 10/04/2021 0 N 1 Y 0 333 11/04/2021 0 N 2 Y 0 333 12/04/2021 0 N 3 Y 0 333 13/04/2021 0 N 4 Y 0 333 14/04/2021 0 N 5 Y 0 333 15/04/2021 0 N 6 Y 0 333 16/04/2021 1 N 6 Y 0 333 17/04/2021 2 N 6 Y 0 333 18/04/2021 3 N 6 Y 0 333 19/04/2021 4 N 0 Y 0 333 20/04/2021 5 N 0 Y 0 333 21/04/2021 0 N 1 Y 0 333 22/04/2021 0 N 2 Y 0 333 23/04/2021 0 N 3 Y 0 333 24/04/2021 0 N 4 Y 0 333 25/04/2021 0 N 5 Y 0 333 26/04/2021 0 N 6 Y 0 333 27/04/2021 0 N 7 Y 0 333 28/04/2021 0 N 8 Y 0 333 29/04/2021 0 N 9 Y 0 333 30/04/2021 0 N 10 Y 0 333 01/05/2021 0 N 0 1 333 02/05/2021 0 N 0 2 333 03/05/2021 0 N 0 3 333 04/05/2021 90 Y 0 0 333 05/05/2021 91 Y 0 0 Sample data code: data sample;
input @1 CID @5 date ddmmyy10. @16 DPD @19 Default_Flag $;
format date ddmmyy10.;
datalines;
111 04/04/2021 87 N
111 05/04/2021 88 N
111 06/04/2021 89 N
111 07/04/2021 90 Y
111 08/04/2021 91 Y
111 09/04/2021 92 Y
111 10/04/2021 93 Y
111 11/04/2021 00 N
111 12/04/2021 00 N
111 13/04/2021 00 N
111 14/04/2021 00 N
111 15/04/2021 00 N
111 16/04/2021 01 N
111 17/04/2021 02 N
111 18/04/2021 00 N
111 19/04/2021 00 N
111 20/04/2021 00 N
111 21/04/2021 00 N
111 22/04/2021 00 N
111 23/04/2021 00 N
111 24/04/2021 00 N
111 25/04/2021 00 N
222 04/04/2021 86 N
222 05/04/2021 87 N
222 06/04/2021 88 N
222 07/04/2021 89 N
222 08/04/2021 90 Y
222 09/04/2021 91 Y
222 10/04/2021 92 Y
222 11/04/2021 93 Y
222 12/04/2021 94 Y
222 13/04/2021 95 Y
222 14/04/2021 96 Y
333 04/04/2021 87 N
333 05/04/2021 88 N
333 06/04/2021 89 N
333 07/04/2021 90 Y
333 08/04/2021 91 Y
333 09/04/2021 92 Y
333 10/04/2021 00 N
333 11/04/2021 00 N
333 12/04/2021 00 N
333 13/04/2021 00 N
333 14/04/2021 00 N
333 15/04/2021 00 N
333 16/04/2021 01 N
333 17/04/2021 02 N
333 18/04/2021 03 N
333 19/04/2021 04 N
333 20/04/2021 05 N
333 21/04/2021 00 N
333 22/04/2021 00 N
333 23/04/2021 00 N
333 24/04/2021 00 N
333 25/04/2021 00 N
333 26/04/2021 00 N
333 27/04/2021 00 N
333 28/04/2021 00 N
333 29/04/2021 00 N
333 30/04/2021 00 N
333 01/05/2021 00 N
333 02/05/2021 00 N
333 03/05/2021 00 N
333 04/05/2021 90 Y
333 05/05/2021 91 Y
;
Run; Thank you so so much in advance for your time and effort.
... View more