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.
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?
Yes. One overlapping period for the same ID
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.