Check eligibility based on enrollment info

[lichee]

Hi all,

I have another task related to enrollment data. In the data I have, each person has one or more enrollment records during the years 2017-2018. I need to identify if a person had at least 365 days of enrollment during the two years, with no more than one gap of maximum 60 days in enrollment. The dummy data is as below:

data have;
infile datalines truncover dsd;
input DummyID (Enroll_beg_dt Enroll_end_dt) (:date9.);
format Enroll_beg_dt Enroll_end_dt date9.;
datalines;
1,01Jan2017,28Feb2018
1,02Mar2018,31Mar2018
1,02Apr2018,08Jul2018
1,01Aug2018,31Dec2018
2,01Jan2017,07Aug2017
2,03Oct2017,31May2018
2,01Oct2018,30Nov2018
3,01Jan2017,28Feb2018
3,02Mar2018,31Mar2018
3,02Apr2018,31Dec2018
4,01Jan2017,31Dec2018
5,01Oct2017,28Feb2018
6,01Jan2017,28Feb2018
6,02Mar2018,31Mar2018
6,02Apr2018,31Dec2018
7,01Apr2018,05Apr2018
7,01May2018,30Sep2018
7,01Nov2018,31Dec2018
8,01Aug2017,31Aug2017
8,08Sep2017,30Sep2017
8,18Oct2017,31Oct2017
8,08Nov2017,30Nov2017
8,08Dec2017,31Dec2017
8,16Jan2018,31Jan2018
8,02Feb2018,28Feb2018
8,02Mar2018,31Mar2018
8,19Apr2018,30Apr2018
8,09May2018,31May2018
8,14Aug2018,31Aug2018
8,27Sep2018,30Sep2018
8,08Nov2018,30Nov2018
;
run;

The target data set is as below:

data want;
infile datalines truncover dsd;
input DummyID Eligible;
datalines;
1,1
2,1
3,1
4,1
5,1
6,1
7,0
8,0
;
run;

Any insight would be greatly appreciated!

[RD2]

data have2 (drop=i);
set have;
by DummyID;
if (first.DummyID or not last.DummyID) then
do i = 1 to 1 + (first.DummyID and not last.DummyID);
set have (keep=Enroll_beg_dt rename=(Enroll_beg_dt=nextval) );
end;
if last.DummyID then
nextval = .;

run;

data have3;
set have2;
dur = Enroll_end_dt - Enroll_beg_dt + 1;
gap = nextval - Enroll_beg_dt;
run;

proc summary data = have3 nway;
class DummyID;
output out=want (DROP = _FREQ_ _TYPE_)
max(gap)=maxgap
sum(dur)=sumdur
;
run;

[Ksharp · Accepted solution]

data have;
infile datalines truncover dsd;
input DummyID (Enroll_beg_dt Enroll_end_dt) (:date9.);
format Enroll_beg_dt Enroll_end_dt date9.;
datalines;
1,01Jan2017,28Feb2018
1,02Mar2018,31Mar2018
1,02Apr2018,08Jul2018
1,01Aug2018,31Dec2018
2,01Jan2017,07Aug2017
2,03Oct2017,31May2018
2,01Oct2018,30Nov2018
3,01Jan2017,28Feb2018
3,02Mar2018,31Mar2018
3,02Apr2018,31Dec2018
4,01Jan2017,31Dec2018
5,01Oct2017,28Feb2018
6,01Jan2017,28Feb2018
6,02Mar2018,31Mar2018
6,02Apr2018,31Dec2018
7,01Apr2018,05Apr2018
7,01May2018,30Sep2018
7,01Nov2018,31Dec2018
8,01Aug2017,31Aug2017
8,08Sep2017,30Sep2017
8,18Oct2017,31Oct2017
8,08Nov2017,30Nov2017
8,08Dec2017,31Dec2017
8,16Jan2018,31Jan2018
8,02Feb2018,28Feb2018
8,02Mar2018,31Mar2018
8,19Apr2018,30Apr2018
8,09May2018,31May2018
8,14Aug2018,31Aug2018
8,27Sep2018,30Sep2018
8,08Nov2018,30Nov2018
;
run;

data temp;
 set have;
 do date=Enroll_beg_dt to Enroll_end_dt;
  output;
 end;
keep DummyID date;
format date date9.;
run;
proc sort data=temp nodupkey ;by  DummyID date;run;
data temp;
 set temp;
 by DummyID;
 if first.DummyID or dif(date)>60 then group+1;
run;
proc sql;
create table want as
select DummyID,max(n)>365 as Eligible from 
(
select DummyID,group,count(*) as n
 from temp
  group by DummyID,group
)
group by DummyID;
quit;

[lichee]

Thanks a lot, Ksharp!

The only part that doesn't work well is for DummyID=2, where its first record has enrollment duration of 218 days, the second record has 240 days, and the gap in between is 57 days (which is less than 60 days). This case the enrollment duration is 218+240=458, so DummyID=2 should be eligible given that it had at least 365 days of enrollment with no more than one gap of maximum of 60 days. 

2,01Jan2017,07Aug2017
2,03Oct2017,31May2018
2,01Oct2018,30Nov2018

 Maybe a small twist may make it work?

[Ksharp]

I don't understand .

My DummyID=2 is eligibled as you showed.

[lichee]

My bad! I used my initial data set. You got it. Thanks a lot!