@novinosrin, You are the true master. Thank you so much for your help as always. ... View more

Hi @novinosrin. I do agree with you totally. I am a noob and I am left alone to tackle this project. I will definitely keep all you mentioned and guided in my mind for future questions. I have checked the code you have provided and it indeed works as I expected. You are a true master and a kind person who spends your own time to help people who are in need (like myself). Thank you so much, Sir. Have a great Sunday. ... View more

@novinosrin First of all, Thank you so so much for your time and help. Unfortunately, the results are not the same as expected. I am sure it is me clumsy in explaining the details. Let me explain in a language you understand best; SAS codes.  Here is the solution you have suggested;   data Master_data; input @1 CID @5 date ddmmyy10. @16 DPD @19 Default_Flag $ latest_flag $; format date ddmmyy10.; datalines; 111 04/04/2021 87 N N 111 05/04/2021 88 N N 111 06/04/2021 89 N N 111 07/04/2021 90 Y N 111 08/04/2021 91 Y N 111 09/04/2021 92 Y N 111 10/04/2021 93 Y N 111 11/04/2021 00 N N 111 12/04/2021 00 N N 111 13/04/2021 00 N N 111 14/04/2021 00 N N 111 15/04/2021 00 N N 111 16/04/2021 01 N N 111 17/04/2021 02 N N 111 18/04/2021 00 N N 111 19/04/2021 00 N N 111 20/04/2021 00 N N 111 21/04/2021 00 N N 111 22/04/2021 00 N N 111 23/04/2021 00 N N 111 24/04/2021 00 N N 111 25/04/2021 00 N Y 222 04/04/2021 86 N N 222 05/04/2021 87 N N 222 06/04/2021 88 N N 222 07/04/2021 89 N N 222 08/04/2021 90 Y N 222 09/04/2021 91 Y N 222 10/04/2021 92 Y N 222 11/04/2021 00 N N 222 12/04/2021 01 N N 222 13/04/2021 02 N N 222 14/04/2021 03 N N 222 15/04/2021 04 N N 222 16/04/2021 05 N Y 333 04/04/2021 87 N N 333 05/04/2021 88 N N 333 06/04/2021 89 N N 333 07/04/2021 90 Y N 333 08/04/2021 91 Y N 333 09/04/2021 92 Y N 333 10/04/2021 00 N N 333 11/04/2021 00 N N 333 12/04/2021 00 N N 333 13/04/2021 00 N N 333 14/04/2021 00 N N 333 15/04/2021 00 N N 333 16/04/2021 01 N N 333 17/04/2021 02 N N 333 18/04/2021 03 Y N 333 19/04/2021 04 Y N 333 20/04/2021 05 Y N 333 21/04/2021 00 Y N 333 22/04/2021 01 N N 333 23/04/2021 02 N N 333 24/04/2021 00 N N 333 25/04/2021 00 N N 333 26/04/2021 00 N N 333 27/04/2021 00 N N 333 28/04/2021 00 N N 333 29/04/2021 00 N N 333 30/04/2021 00 N N 333 01/05/2021 00 N N 333 02/05/2021 00 N N 333 03/05/2021 00 N N 333 04/05/2021 90 Y Y 444 10/12/2021 00 N N 444 11/12/2021 90 Y N 444 12/12/2021 91 Y Y ; Run; Data latest_data; set master_data(where=(latest_flag="Y")); run; proc sort data=latest_data; by cid date; 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 555 07/04/2021 00 Y 555 08/04/2021 00 N 555 09/04/2021 00 N ; Run; proc sort data=new_data out=_new_data; by cid date; run; data combine; set latest_data _new_data; by cid date; run; data new_want; if 0 then set combine; Probation_count=0; length Probation_Flag $1; Cure_count=0; do until(last.Default_Flag); set combine; by cid Default_Flag notsorted; if Default_Flag='N' and first.Default_Flag 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; proc sql; create table latest_after_execution as select * from new_want group by cid having date=max(date); quit; This is what I would expect to get as a results;   data combine_new; set master_data(drop=latest_flag) _new_data; by cid date; run; proc sort data=combine_new out=_combine_new; by cid date; run; data want; if 0 then set _combine_new; Probation_count=0; length Probation_Flag $1; Cure_count=0; do until(last.Default_Flag); set _combine_new; 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; proc sql; create table unwantedmethod_after_execution as select * from want group by cid having date=max(date); quit; I want the table latest_after_execution to be same as table unwantedmethod_after_execution.  Note that Master table has been executed and contains probation_count, probation_flag and cure_count.    If I am gonna compute the variables for new_data, the following would be my initial table data initial_table; 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;   I really really hope I have made myself clear now. If not I will try harder.    Thank you so so much for your help.  ... View more

@novinosrin Thank you so much for help. I will check and get back to you. ... View more

Hi @novinosrin. Thank you so much for trying to help me. Have a great day Sir. ... View more

@novinosrin I have a latest_flag in the master_table already. Therefore, we can fetch the latest information from master_table by filtering. 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. I have updated the master_data accordingly. data Master_data; input @1 CID @5 date ddmmyy10. @16 DPD @19 Default_Flag $ latest_flag $; format date ddmmyy10.; datalines; 111 04/04/2021 87 N N 111 05/04/2021 88 N N 111 06/04/2021 89 N N 111 07/04/2021 90 Y N 111 08/04/2021 91 Y N 111 09/04/2021 92 Y N 111 10/04/2021 93 Y N 111 11/04/2021 00 N N 111 12/04/2021 00 N N 111 13/04/2021 00 N N 111 14/04/2021 00 N N 111 15/04/2021 00 N N 111 16/04/2021 01 N N 111 17/04/2021 02 N N 111 18/04/2021 00 N N 111 19/04/2021 00 N N 111 20/04/2021 00 N N 111 21/04/2021 00 N N 111 22/04/2021 00 N N 111 23/04/2021 00 N N 111 24/04/2021 00 N N 111 25/04/2021 00 N Y 222 04/04/2021 86 N N 222 05/04/2021 87 N N 222 06/04/2021 88 N N 222 07/04/2021 89 N N 222 08/04/2021 90 Y N 222 09/04/2021 91 Y N 222 10/04/2021 92 Y N 222 11/04/2021 00 N N 222 12/04/2021 01 N N 222 13/04/2021 02 N N 222 14/04/2021 03 N N 222 15/04/2021 04 N N 222 16/04/2021 05 N Y 333 04/04/2021 87 N N 333 05/04/2021 88 N N 333 06/04/2021 89 N N 333 07/04/2021 90 Y N 333 08/04/2021 91 Y N 333 09/04/2021 92 Y N 333 10/04/2021 00 N N 333 11/04/2021 00 N N 333 12/04/2021 00 N N 333 13/04/2021 00 N N 333 14/04/2021 00 N N 333 15/04/2021 00 N N 333 16/04/2021 01 N N 333 17/04/2021 02 N N 333 18/04/2021 03 Y N 333 19/04/2021 04 Y N 333 20/04/2021 05 Y N 333 21/04/2021 00 Y N 333 22/04/2021 01 N N 333 23/04/2021 02 N N 333 24/04/2021 00 N N 333 25/04/2021 00 N N 333 26/04/2021 00 N N 333 27/04/2021 00 N N 333 28/04/2021 00 N N 333 29/04/2021 00 N N 333 30/04/2021 00 N N 333 01/05/2021 00 N N 333 02/05/2021 00 N N 333 03/05/2021 00 N N 333 04/05/2021 90 Y Y 444 10/12/2021 00 N N 444 11/12/2021 90 Y N 444 12/12/2021 91 Y Y ; Run; Data latest_data; set master_data(where=(latest_flag="Y")); run; proc sort data=latest_data; by cid date; 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 555 07/04/2021 00 Y 555 08/04/2021 00 N 555 09/04/2021 00 N ; Run;  using latest_data as a "start" to process new_data. logic needs to be adjusted to take the starting points as follows; 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; Please let me know, I am still being unclear. Thank you so much for your help.    ... View more

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

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

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

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

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

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

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

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