Conditionally populate values while service date is less then than a given date

[SmellyJim]

Hi, 

I have the following table:

and I would like the following output:

I would like any records where the service dates are between the first and last dates for each event number to also be populated with the same event number. For example, Event_no = 1 has a date range of 1st Jan 2020 to 3rd Jan 2020. I would like all records between those dates (inclusive) to be tagged as Event_no = 1. Then so on for Event_no = 2 etc. There will be hundreds of distinct Event_nos in the data. 

If it helps to populate by different means, the Event_nos are determined by the Code AAA occurring on consecutive days. Ie, Event_No 1 occurs on 1st, 2nd and 3rd of January 2020.

I've tried a while loop but it seems to get stuck in an infinite loop, I'm not sure how to resolve. Any suggestions would be much appreciated.

Many thanks!

[PeterClemmensen · Accepted solution]

Try this

data have;
input id code $ service_dt :date9. event_no max_service_dt :date9.;
format service_dt max_service_dt date11.;
datalines;
1  AAA 01-Jan-20 1 03-Jan-20 
2  BBB 01-Jan-20 . .         
3  CCC 02-Jan-20 . .         
4  AAA 02-Jan-20 1 03-Jan-20 
5  DDD 02-Jan-20 . .         
6  EEE 02-Jan-20 . .         
7  AAA 03-Jan-20 1 03-Jan-20 
8  FFF 03-Jan-20 . .         
9  GGG 05-Jan-20 . .         
10 HHH 06-Jan-20 . .         
11 III 06-Jan-20 . .         
12 JJJ 08-Jan-20 . .         
13 AAA 08-Jan-20 2 09-Jan-20 
14 KKK 08-Jan-20 . .         
15 LLL 09-Jan-20 . .         
16 AAA 09-Jan-20 2 09-Jan-20 
;

data want(drop = e m);
   set have;
   if event_no then do;
      e = event_no;
      m = max_service_dt;
   end;
   
   if service_dt <= m then event_no = e;

   retain e m;
run;

Result:

id  code  service_dt   event_no  max_service_dt
1   AAA   01-JAN-2020  1         03-JAN-2020
2   BBB   01-JAN-2020  1         .
3   CCC   02-JAN-2020  1         .
4   AAA   02-JAN-2020  1         03-JAN-2020
5   DDD   02-JAN-2020  1         .
6   EEE   02-JAN-2020  1         .
7   AAA   03-JAN-2020  1         03-JAN-2020
8   FFF   03-JAN-2020  1         .
9   GGG   05-JAN-2020  .         .
10  HHH   06-JAN-2020  .         .
11  III   06-JAN-2020  .         .
12  JJJ   08-JAN-2020  .         .
13  AAA   08-JAN-2020  2         09-JAN-2020
14  KKK   08-JAN-2020  2         .
15  LLL   09-JAN-2020  2         .
16  AAA   09-JAN-2020  2         09-JAN-2020

[SmellyJim]

Amazing! Thanks for your time and knowledge Peter. Super helpful.