Identifying period of overlap across multiple observations

[altijani]

Hello Community

I have the following data that I need to use to measure the periods of membership overlap (if any) for the same individual but in multiple stores:

Obs_Num	Store_ID	Member_ID	Member_From	Member_To	Member_Type
1	AA	123	01/01/2020	12/31/2021	Full
2	BB	123	06/01/2021	12/31/2999	Full
3	AA	456	01/01/2020	12/31/2021	Full
4	BB	456	09/01/2021	10/31/2021	Part
5	AA	789	01/01/2020	12/31/2021	Full
6	BB	911	06/01/2021	10/01/2021	Part
7	AA	411	01/01/2020	12/31/2022	Full
8	BB	411	09/01/2021	12/31/2099	Part
9	CC	411	12/01/2021	12/31/2022	Full

Few notes (the colors are to clarify the overlapping):

- Observations 1&2 are for the same ID in 2 different stores

- Observations 5& 6 are for distinct ID and for only one stores (no overlap)

- Observations 7-8-9 are for the same ID in 3 different stores

- The new dataset (below) should have 5 new columns to measure overlap from start UNTILL the end of 2021.

- The new column "Type_Overlap" is tricky in that it has to start with that store's membership type first. For example, Obs 7/8/9 are similar in 4 of the new 5 columns EXCEPT the type_overlap.

The new columns to be added to the data should be as follows:

Obs_Num	Store_ID	Member_ID	Member_From	Member_To	Member_Type	Mnths_Overlaps_till2021	Num_Stores_Overlap	Type_Overlap	Overlap_From	Overlap_To
1	AA	123	01/01/2020	12/31/2021	Full	7	2	Full/Full	06/01/2021	12/31/2021
2	BB	123	06/01/2021	12/31/2999	Full	7	2	Full/Full	06/01/2021	12/31/2021
3	AA	456	01/01/2020	12/31/2021	Full	2	2	Full/Part	09/01/2021	10/31/2021
4	BB	456	09/01/2021	10/31/2021	Part	2	2	Part/Full	09/01/2021	10/31/2021
5	AA	789	01/01/2020	12/31/2021	Full	0	0	Full
6	BB	911	06/01/2021	10/01/2021	Part	0	0	Part
7	AA	411	01/01/2020	12/31/2022	Full	4	3	Full/Part/Full	09/01/2021	12/31/2021
8	BB	411	09/01/2021	12/31/2099	Part	4	3	Part/Full/Full	09/01/2021	12/31/2021
9	CC	411	12/01/2021	12/31/2022	Full	4	3	Full/Full/Part	09/01/2021	12/31/2021

Is there a way to do this in SAS in a meaningful code? My data is a lot much larger than the above, but I hope I captured all the scenarios.

[mkeintz]

In the case of members with overlap, will the data always show a single overlap period?

In other words, can you say that the data will never show an instance of two (or more) non-contiguous overlaps?  If the data can present non-contiguous overlaps periods, how would you determine values for overlap_from and overlap_to?

[altijani]

Yes. One overlapping period for the same ID