The ids in group 5 (1-7 and 1-8) are not in Newgroup -2. So only it is not in newgroup2 though time window overlap.  New groups should be assigned based on both conditions checking ids present in it and also time overlap.   Thank you   @ChrisNZ  ... View more

Hi All, The data needs to be grouped based on conditions.   data have; input id $ group (start_time end_time) (:datetime19.); format start_time end_time e8601dt19.; datalines; 1-10 1 27FEB22:12:32:00 27FEB22:13:04:00 1-9 1 27FEB22:12:32:00 27FEB22:13:04:00 2-15 1 27FEB22:12:33:00 27FEB22:13:12:00 1-4 2 27FEB22:10:00:00 27FEB22:10:51:00 1-5 2 27FEB22:10:00:00 27FEB22:10:51:00 1-5 3 27FEB22:10:27:00 27FEB22:11:39:00 1-6 3 27FEB22:10:27:00 27FEB22:11:39:00 1-6 4 25FEB22:09:18:00 25FEB22:09:33:00 1-7 4 25FEB22:09:18:00 25FEB22:09:33:00 1-7 5 27FEB22:11:33:00 27FEB22:12:39:00 1-8 5 27FEB22:11:33:00 27FEB22:12:39:00 1-8 6 27FEB22:11:34:00 27FEB22:12:39:00 1-9 6 27FEB22:11:34:00 27FEB22:12:39:00 103-21 7 05APR22:12:26:00 05APR22:13:02:00 103-22 7 05APR22:12:26:00 05APR22:13:02:00 102-58 8 05APR22:12:43:00 05APR22:13:06:00 103-22 8 05APR22:12:43:00 05APR22:13:06:00 103-17 9 05APR22:11:26:00 05APR22:12:34:00 103-19 9 05APR22:11:26:00 05APR22:12:34:00 103-19 10 05APR22:11:27:00 05APR22:12:52:00 103-21 10 05APR22:11:27:00 05APR22:12:52:00 ; run; Expected Output: Grouping needs to be done based on below conditions. 1. We are forming the variable new_group based on a time span covered by all members of a group. (time span is defined as the earliest start and latest end of all the observations in a group) 2. all members of a group will be assigned to the same new_group. 3. The first group will be assigned newgroup=1. 4. If the earliest start_time or last end_time of a group falls in between already formed newgroup's earliest start_time and last end_time and also id's of the group also match newgroup then it should be included with the newgroup. Otherwise, it should create a new newsgroup.   Note: 1.Eventhough group 2 and 3 have same ids , they should not come under same new_group because both groups are having different time and they did not intersect/overlap. 2. Single id value should not come under multiple new_groups.   Please help on this. Thank you. ... View more