BookmarkSubscribeRSS Feed
altijani
Quartz | Level 8

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_NumStore_IDMember_IDMember_FromMember_ToMember_Type
1AA12301/01/202012/31/2021Full
2BB12306/01/202112/31/2999Full
3AA45601/01/202012/31/2021Full
4BB45609/01/202110/31/2021Part
5AA78901/01/202012/31/2021Full
6BB91106/01/202110/01/2021Part
7AA41101/01/202012/31/2022Full
8BB41109/01/202112/31/2099Part
9CC41112/01/202112/31/2022Full

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_NumStore_IDMember_IDMember_FromMember_ToMember_TypeMnths_Overlaps_till2021Num_Stores_OverlapType_OverlapOverlap_FromOverlap_To
1AA12301/01/202012/31/2021Full72Full/Full06/01/202112/31/2021
2BB12306/01/202112/31/2999Full72Full/Full06/01/202112/31/2021
3AA45601/01/202012/31/2021Full22Full/Part09/01/202110/31/2021
4BB45609/01/202110/31/2021Part22Part/Full09/01/202110/31/2021
5AA78901/01/202012/31/2021Full00Full  
6BB91106/01/202110/01/2021Part00Part  
7AA41101/01/202012/31/2022Full43Full/Part/Full09/01/202112/31/2021
8BB41109/01/202112/31/2099Part43Part/Full/Full09/01/202112/31/2021
9CC41112/01/202112/31/2022Full43Full/Full/Part09/01/202112/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.

 

2 REPLIES 2
mkeintz
PROC Star

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?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
altijani
Quartz | Level 8

Yes. One overlapping period for the same ID

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 337 views
  • 0 likes
  • 2 in conversation