Assuming that no store_id occurs more than once per member_id (so that there won't be a false overlap inferred), then a DATA step passing through each ID twice will do.
It uses a 2-way array indexed by YEAR and MONTH to track overlaps.
/*Combine and Condense Duplicate Records*/
%let HLB=2019; /* History lower bound */
%let HUB=2021; /* History upper bound */
data have;
infile cards expandtabs truncover;
input Obs_Num Store_ID $ Member_ID $ Member_From :mmddyy10. Member_To :mmddyy10. Member_Type $;
format Member_From Member_To mmddyy10.;
cards;
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
;
data want (drop=_:);
/** Do a first pass through a single ID **/
set have;
by member_id notsorted;
array mfreq{&HLB:&HUB,12} _temporary_ /*Frequency by year and month */;
retain mnths_overlaps_till2021 num_stores_overlap type_overlap overlap_from overlap_to;
length type_overlap $20;
format overlap_from overlap_to mmddyy10. ;
if first.member_id then do;
call missing(of mfreq{*},of mnths_overlaps_till2021 -- overlap_to);
mnths_overlaps_till2021 = 0;
end;
num_stores_overlap + not(first.member_id=1 and last.member_id=1);
type_overlap=catx('/',type_overlap,member_type);
_mdate=member_from;
do while (_mdate< min(member_to,"31dec&HUB"d));
_y=year(_mdate);
_m=month(_mdate);
mfreq{_y,_m}+1;
if mfreq{_y,_m}=2 then do;
overlap_from=min(overlap_from,mdy(_m,1,_y));
overlap_to=max(overlap_to,intnx('month',mdy(_m,1,_y),0,'end'));
mnths_overlaps_till2021 = intck('month',overlap_from,overlap_to)+1;
end;
_mdate=intnx('month',_mdate,1);
end;
/* Do a second pass through the same ID, and output */
if last.member_id then do _i=1 to coalesce(dif(_n_),_n_);
set have;
output;
end;
run;
Also, this assumes that all overlaps are nested (e.g. for 3 stores A,B, and C: A/B overlap for 4 months, and A/C and B/C overlap 1 month within that 4 month A/B overlap). And you want the longest span of those nested overlaps.
... View more