Thank you for your approach, it does not always work for my scenarios. But I solved it differently. I firstly created a datafile with all ID's and months going forward, then I only join the desired entries to my "base table". ... View more

Ok thanks for the feedback, I try to give some more details and then it may become clearer: The goal at the end would be, to have a table which shows per id only one entry of the startmonth and the corresponding flag. The flag should only show 1 if (based on the have table) the underlying event ocurred. Since the have table can contain multiple entries per id (since the event can happen more than once within the underlying period), I want to sort of "overwrite" the information for a already existing startmonth if the have table was update and a new dataline is included. That is the reason for id=1,MAR2021, number=2 not beeing included in the desired table.  Based on the entry  1 01JAN2021 2 from table have, MAR2021 would show flag=0. Since entry 1 01APR2021 1 also exists, MAR2021 would not have flag=0 anymore but now flag=1. As long as the second entry for id=1 does not exist it is no problem that flag=0 for id=1 in MAR2021. If for example the second entry for id=1 would be 1 01FEB2021 2 then I would need to have NOV2020, DEC2020 and JAN2021 with flag=1 and the rest until end date with flag=0.  Does that give you more details to the desired outcome?   I am not quite sure what exactly you mean by "look ahead".  ... View more

Thank you for your answer. My example for Id=1 does not include any data for 2020 because that is exactly how the data is set up, for some id's there is no datapoint before. 01JUN2021 was set randomly, it could very well also be another date, it always would be based on a predefined report date, in this case it was 01JUN2021.  Your code works very well, thank you very much!   I have one follow-up question where you maybe also have an idea on how to solve it. If for the same id I would multiple entries e.g. for id=1 for APR2021: data have; input id date: date9. number; format date date9.; datalines; 1 01JAN2021 2 2 01FEB2021 1 3 01JUN2021 3 1 01APR2021 1 ; run; My result will have double entries for the month's MAR2021 until JUN2021, with the entry for MAR2021 one time with flag=1 and the other entry with a flag=0. Is there a way to solve that in a way so that the end table would look like this: id date number startmonth flag 1 01Jan2021 2 01Nov2020 1 1 01Jan2021 2 01Dec2020 1 1 01Jan2021 2 01Jan2021 0 1 01Jan2021 2 01Feb2021 0 1 01Apr2021 1 01Mar2021 1 1 01Apr2021 1 01Apr2021 0 1 01Apr2021 1 01May2021 0 1 01Apr2021 1 01Jun2021 0 instead of this: id date number startmonth flag 1 01Jan2021 2 01Nov2020 1 1 01Jan2021 2 01Dec2020 1 1 01Jan2021 2 01Jan2021 0 1 01Jan2021 2 01Feb2021 0 1 01Jan2021 2 01Mar2021 0 1 01Jan2021 2 01Apr2021 0 1 01Jan2021 2 01May2021 0 1 01Jan2021 2 01Jun2021 0 1 01Apr2021 1 01Mar2021 1 1 01Apr2021 1 01Apr2021 0 1 01Apr2021 1 01May2021 0 1 01Apr2021 1 01Jun2021 0   So to say that if there is a new entry which would implicate another information for the same event but does not overwrite the already existing entries. So in this case the event for ID=1 occurred for NOV2020 and DEC2020 and again for MAR2021. ... View more

Thank you @Kurt_Bremser and @Reeza for your help! The solution @Kurt_Bremser presented suits me the most. ... View more

Thank you @Reeza for your quick respond. I indeed want the overall sum by id up to every month. Meaning for id=1 I want the sum_all=10 for DEC2020, for JAN2021 the amount=0 therefore my sum_all=10, for FEB2021 amount=5 and therefore my desired value is sum_all=15 (10+0+5). So I always want the running total for each id. Missing values are ok (I would manipulate the final dataset with a PROC STDIZE) but ideally it does not contain any missings. Does that mean, that there is no other solution than to first sort the dataset and and use SUM and/or RETAIN? There is no possibilty to do it in one step? ... View more