Here is one approach. It might be easier to transpose your registration data, especially if start and end dates cover multiple years. I have assumed below that part month registrations should be included. data member ;
infile cards dlm=',' ;
input id StartDate:mmddyy10. EndDate:mmddyy10. ;
StartMonth=intnx('MONTH',StartDate,0,'B') ;
EndMonth=intnx('MONTH',EndDate,0,'B') ;
format StartDate EndDate StartMonth EndMonth ddmmyy10. ;
cards ;
1,3/19/2018,6/01/2018
1,7/7/2019,8/21/2019
2,3/6/2019,9/01/2019
;
run ;
data registration ;
infile cards dlm=',' ;
input person_id year jan feb mar apr may jun jul aug sep oct nov dec ;
cards ;
1,2018,0,0,0,1,1,0,0,0,0,0,0,1
1,2019,1,1,1,1,1,1,1,1,1,1,1,1
2,2018,1,1,0,0,1,1,1,1,1,1,1,1
2,2019,1,1,1,1,1,1,0,0,0,0,0,0
;
run;
data registrationTransposed ;
set registration ;
array mon(12) jan feb mar apr may jun jul aug sep oct nov dec ;
do i=1 to 12 ;
date=mdy(i,1,year) ;
Flag=mon(i) ;
output ;
end ;
keep person_id date flag ;
format date monyy7. ;
run ;
proc sql ;
create table want as select distinct
m.ID
,m.StartDate
,m.EndDate
from member as m
inner join
registrationTransposed as r
on
m.id=r.Person_ID
and r.Date between m.StartMonth and m.EndMonth
group by 1,2,3
having sum(Flag)=intck('MONTH',m.StartMonth,m.EndMonth)+1 ;
;
quit ;
... View more
