topic Re: Creating complex logic counter variables in SAS Programming

Creating complex logic counter variables

Myurathan — Wed, 16 Sep 2020 15:05:35 GMT

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.

Re: Creating complex logic counter variables

andreas_lds — Wed, 16 Sep 2020 06:29:33 GMT

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

Re: Creating complex logic counter variables

Myurathan — Wed, 16 Sep 2020 06:36:15 GMT

@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.

Re: Creating complex logic counter variables

Kurt_Bremser — Wed, 16 Sep 2020 06:54:36 GMT

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.

Re: Creating complex logic counter variables

Myurathan — Wed, 16 Sep 2020 07:21:49 GMT

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

Re: Creating complex logic counter variables

Myurathan — Wed, 16 Sep 2020 09:11:13 GMT

@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.

Re: Creating complex logic counter variables

novinosrin — Wed, 16 Sep 2020 12:10:21 GMT

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

Re: Creating complex logic counter variables

Myurathan — Wed, 16 Sep 2020 12:15:00 GMT

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

Re: Creating complex logic counter variables

novinosrin — Wed, 16 Sep 2020 14:42:13 GMT

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
333	11/04/2021	0	N	2	Y
333	12/04/2021	0	N	3	Y
333	13/04/2021	0	N	4	Y
333	14/04/2021	0	N	5	Y
333	15/04/2021	0	N	6	Y
333	16/04/2021	1	N	5	Y
333	17/04/2021	2	N	5	Y
333	18/04/2021	3	N	5	Y
333	19/04/2021	4	N	0	Y
333	20/04/2021	5	N	0	Y
333	21/04/2021	0	N	1	Y
333	22/04/2021	0	N	2	Y
333	23/04/2021	0	N	3	Y
333	24/04/2021	0	N	4	Y
333	25/04/2021	0	N	5	Y

Re: Creating complex logic counter variables

Myurathan — Wed, 16 Sep 2020 15:02:12 GMT

@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.

Re: Creating complex logic counter variables

Myurathan — Wed, 16 Sep 2020 15:13:39 GMT

@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

Re: Creating complex logic counter variables

novinosrin — Wed, 16 Sep 2020 16:21:31 GMT

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

Re: Creating complex logic counter variables

Myurathan — Wed, 16 Sep 2020 16:57:24 GMT

@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.

Re: Creating complex logic counter variables

Myurathan — Thu, 17 Sep 2020 13:09:35 GMT

@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
333	16/04/2021	1	N	6	Y
333	17/04/2021	2	N	6	Y
333	18/04/2021	3	N	6	Y

I want like this;

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

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

Thanks in advance.

Re: Creating complex logic counter variables

novinosrin — Thu, 17 Sep 2020 14:03:11 GMT

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?

Re: Creating complex logic counter variables

Myurathan — Thu, 17 Sep 2020 14:26:09 GMT

@novinosrin I have edited the code and it works as I expect.
I have edited the following part of the code.
if _k then do;
if /*not dpd and*/ _k1=. then do;
Probation_count+1;
Probation_Flag='Y';
end;
Thanks for the consideration. Have a great day.

Re: Creating complex logic counter variables

Myurathan — Wed, 23 Sep 2020 08:02:07 GMT

@novinosrin Thank you so much for your generous help.

I have the following question to adjust the code to include new_data into the master_data. Since I cannot run the logic after appending the tables, I would like to do the calculation only on the new_data table and then append this to the master table.

Master_Table, new_table and the code to get the master_table with the calculated variables (probation_count, probation_flag, and cure_count)

data Master_data;
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
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 00 N
222 12/04/2021 01 N
222 13/04/2021 02 N
222 14/04/2021 03 N
222 15/04/2021 04 N
222 16/04/2021 05 N
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 Y
333 19/04/2021 04 Y
333 20/04/2021 05 Y
333 21/04/2021 00 Y
333 22/04/2021 01 N
333 23/04/2021 02 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
111 25/04/2021 00 N
444 10/12/2021 00 N
444 11/12/2021 90 Y
444 12/12/2021 91 Y


;
Run;

proc sort data=master_data;
by cid date;
run;

data want;
 if 0 then set master_data;
  Probation_count=0;
  length Probation_Flag $1;
  Cure_count=0;
  do until(last.Default_Flag);
   set master_data;
   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 _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;

data New_data;
input @1 CID @5 date ddmmyy10. @16 DPD @19 Default_Flag $;
format date ddmmyy10.;
datalines;
111 26/04/2021 00 N
333 05/05/2021 91 Y
222 17/04/2021 06 Y
444 13/12/2021 00 N

;
Run;

I want the code to pick the latest values from master table and continue the calculations in the new_data table.

I am in a same situation as this thread https://communities.sas.com/t5/SAS-Programming/Append-two-tables-and-increment-count-on-the-new-data/m-p/675503#M203555 , where you helped me to solve.

Thank you so much again for your help.

Re: Creating complex logic counter variables

novinosrin — Wed, 23 Sep 2020 12:13:24 GMT

Good morning @Myurathan , Not sure if I am reading your points correctly. Please clarify

1. "I want the code to pick the latest values from master table and continue the calculations in the new_data table. "-- Do you want to replicate the same solution in the other thread just using the Master_table and New_data.

2. Does the existing WANT code you posted has got to do anything in this ? or is the WANT table that goes in as Master table?

3 Or is it a rewrite of the same WANT code logic here, however with inclusion of New_data?

Can you please make it clearer

Re: Creating complex logic counter variables

Myurathan — Wed, 23 Sep 2020 12:33:26 GMT

@novinosrin Good morning! Sorry for not being precise. Let me answer your questions below.
1. "I want the code to pick the latest values from master table and continue the calculations in the new_data table. "-- Do you want to replicate the same solution in the other thread just using the Master_table and New_data.
Ans- YES, I have already run the code for the Master_data table. So the Master_data table has calculated variables (probation_count, probation_flag and cure_count).
Now I have the latest data in a new_data table.
One way to do achieve the solution is appending master_data table and new_data table, and then running the (WANT) code. Unfortunately, master_data table is huge and I do not want to run the code again on it.
Instead, I want the code that picks the latest probation_count and cure_count for the given CID from master_table and continue the calculation in the new_data table creating probation_count, probation_flag and cure_count.

2. "I want the code to pick the latest values from master table and continue the calculations in the new_data table. "-- Do you want to replicate the same solution in the other thread just using the Master_table and New_data.
ans- NO, in the other thread, you helped me achieve the same thing with a different code. there also I had a code that needs to be using the latest value from another table and continue the calculation in the new table.

3 Or is it a rewrite of the same WANT code logic here, however with inclusion of New_data?
ans- YES

Re: Creating complex logic counter variables

novinosrin — Wed, 23 Sep 2020 14:48:58 GMT

Hi @Myurathan Thank you.

To address " I want the code that picks the latest probation_count and cure_count for the given CID from master_table and continue the calculation in the new_data table creating probation_count, probation_flag and cure_count." , you would still need to read and process the master_table to determine/capture the record that has the latest probation_count and cure_count for the given CID.

Of course you may do so by -

proc sql;
create table latest as
select *
from want
group by cid
having date=max(date);
quit;

which will give you the latest for each CID i.e. -

CID	date	DPD	Default_Flag	Probation_count	Probation_Flag	Cure_count
111	25/04/2021	0	N	0		5
222	16/04/2021	5	N	0	Y	0
333	4/5/2021	90	Y	0		0
444	12/12/2021	91	Y	0		0

Then we can proceed with a LOOKUP/JOIN operation from New_data to the above result fetching the latest information for each CID and continue with the necessary calculations from there onwards.

However, I would like you to be aware that in any case, there is a requirement to have at least one pass of the dataset WANT aka Master table with the count info to fetch the latest record and then use that as a "start" to process new_data.

Please think through, review your requirement and let me know what you think is best for you. I hope you get what I am saying to make sense of it.