Creating complex logic counter variables

[Myurathan]

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. 

[andreas_lds]

Please edit your message and change the beautiful table to a data step using datalines, so that the data is actually useful.

[Myurathan]

@andreas_lds I have added a link to the data from an Excel file. Hope it is adequate to get the data into SAS. Thanks for your suggestion. 

[Kurt_Bremser]

If you need help with an Excel spreadsheet, you should go to a Microsoft-centered forum. If you actually need assistance with SAS, then post your dataset in a DATA STEP WITH DATALINES, as requested. Spreadsheets are not useful in representing datasets, and cost extra work bringing the data into SAS. Help us to help you.

[Myurathan]

@Kurt_Bremser @andreas_lds  I have added the input dataline code in the post. Thank you in advance. 

[Myurathan]

@novinosrin you have helped me same kind of questions before, therefore, I am wondering is it something you can help me with? Thank you so much for you help so far. 

[novinosrin]

Good morning @Myurathan  Just got in to work. Will take a look shortly, perhaps just after breakfast and respond. Have a good day!

[Myurathan]

@novinosrin, You are the best. You too have a great day!

[novinosrin]

Hi @Myurathan  Just had a look at your description and the sample. I am afraid your results for 333 is something I am not clear with my understanding. Can you please explain the below and narrate the logic/results for 333-

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	5	Y	0
333	17/04/2021	2	N	5	Y	0
333	18/04/2021	3	N	5	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

[Myurathan]

@novinosrin, You are absolutely correct. I have made a mistake when manually calculating the fields. Let me correct right away. Sorry for the mistake, and thanks for the effort.

[Myurathan]

@novinosrin, I have corrected.
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

[novinosrin · Accepted solution]

HI @Myurathan  Please test the following carefully and let know if you think some changes/tweak may be required. I am afraid I won't be able to devote much time to test as I am swamped with my day's work commitment, however I can and I will after 5 pm(Eastern time-USA). 

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;


data want;
 if 0 then set sample;
  Probation_count=0;
  length Probation_Flag $1;
  Cure_count=0;
  do until(last.Default_Flag);
   set sample;
   by cid Default_Flag notsorted;
   if Default_Flag='N' and first.Default_Flag and not first.cid then _k=1;
   else if Default_Flag='Y' then _k=.;
   if _k then do;
    if not dpd and _k1=. then do;
     Probation_count+1;
	 Probation_Flag='Y';
	end;
   	if Probation_count>10 then _k1=1;
	if _k1 then do;
     Probation_Flag=' ';
	 if _k2=. then Cure_count+1;
	 else Cure_count=0;
	end;
	if Cure_count=10 then _k2=1;
	if dpd>3 or Probation_count>10 then Probation_count=0;
   end;
   output;
  end;
  drop _:;
run;
proc print noobs;run;

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

[Myurathan]

@novinosrin It worked like a charm. You are the best. I do not know how to thank you. Keep up your good work to help noobs like myself. Thanks again, Sir.

[Myurathan]

@novinosrin If I have to remove the part it keeps the last period probation_count when dpd>0 and DPD<

31. 

Currently, it looks like this:

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

I want like this;

333	15/04/2021	0	N	6	Y	0
333	16/04/2021	1	N	7	Y	0
333	17/04/2021	2	N	8	Y	0
333	18/04/2021	3	N	9	Y	0

Keeping all other condition valid. how can I adjust the code?

Thanks in advance. 

[novinosrin]

Hi @Myurathan  Hands tied between meetings after meetings until 1 pm eastern time. Will respond just after. 

BTW, can you please expand a little more with context on this need more comprehensively to help me understand in full?