topic Re: Check eligibility based on enrollment info in SAS Programming

Check eligibility based on enrollment info

lichee — Wed, 07 Dec 2022 03:53:57 GMT

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!

Re: Check eligibility based on enrollment info

RD2 — Wed, 07 Dec 2022 06:55:35 GMT

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;

Re: Check eligibility based on enrollment info

Ksharp — Wed, 07 Dec 2022 11:47:56 GMT

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;

Re: Check eligibility based on enrollment info

lichee — Wed, 07 Dec 2022 15:21:35 GMT

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?

Re: Check eligibility based on enrollment info

Ksharp — Thu, 08 Dec 2022 11:40:39 GMT

I don't understand .

My DummyID=2 is eligibled as you showed.

Re: Check eligibility based on enrollment info

lichee — Thu, 08 Dec 2022 13:38:32 GMT

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