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 have2;
set have;
if Member_To>'31dec2021'd then Member_To='31dec2021'd;
run;
data temp;
set have2;
do date= Member_From to Member_To;
output;
end;
format date mmddyy10.;
drop Member_From Member_To;
run;
proc sort data=temp out=temp2 nodupkey;
by Member_ID date Obs_Num Store_ID;
run;
data temp3;
do until(last.date);
set temp2;
by Member_ID date ;
length _Obs_Num _Store_ID _Member_Type $ 200;
_Obs_Num=catx('|',_Obs_Num,Obs_Num);
_Store_ID=catx('|',_Store_ID,Store_ID);
_Member_Type=catx('|',_Member_Type,Member_Type);
end;
if findc(_Obs_Num,'|') then output;
drop Obs_Num Store_ID Member_Type;
run;
data temp4;
set temp3;
by Member_ID date ;
if first.Member_ID or dif(date) ne 1 then group+1;
run;
proc sql;
create table want1 as
select group,Member_ID,min(date) as Overlap_From format=mmddyy10.,max(date) as Overlap_To format=mmddyy10.,
intck('month',calculated Overlap_From,calculated Overlap_To,'c')+1 as Mnths_Overlaps_till2021,
max(countw(_Store_ID,'|')) as Num_Stores_Overlap
from temp4
group by group,Member_ID;
create table want2 as
select a.*,group,Overlap_From,Overlap_To,
coalesce(Mnths_Overlaps_till2021,0) as Mnths_Overlaps_till2021,
coalesce(Num_Stores_Overlap,0) as Num_Stores_Overlap
from have as a left join want1 as b
on a.Member_ID=b.Member_ID
order by Obs_Num;
create table temp5 as
select distinct group,Member_ID,_Obs_Num,_Member_Type
from temp4
group by group,Member_ID
having countw(_Obs_Num,'|')=max(countw(_Obs_Num,'|'));
create table want3 as
select a.*,_Obs_Num,coalescec(_Member_Type,Member_Type) as _Member_Type
from want2 as a left join temp5 as b
on a.group=b.group and a.Member_ID=b.Member_ID
order by Obs_Num;
quit;
data want4;
set want3;
length Type_Overlap $ 200;
if missing(_Obs_Num) or input(scan(_Obs_Num,1,'|'),best.)=Obs_Num then Type_Overlap=_Member_Type;
else do;
p=findw(_Obs_Num,strip(Obs_Num),'|','eit');
Type_Overlap=scan(_Member_Type,p,'|');
do i=1 to countw(_Member_Type,'|');
if i ne p then Type_Overlap=catx('|',Type_Overlap,scan(_Member_Type,i,'|'));
end;
end;
drop p i _Obs_Num _Member_Type;
run;
Click image to register for webinar
