Continuous enrollment across years following a birth event pt2

[Wolverine]

In my previous post, I laid out the issue and provided example data. Unfortunately, there was a use case that I didn't think of. @ballardw , you helped me with the original solution, so I'm hoping you can help me tweak it to address this issue.

But first, let me clarify that we've shortened our follow-up period from 12 months to 3 months. If the delivery date is 11/10/2018, the case needs to have continuous enrollment in Dec 2018, Jan 2019, and Feb 2019. If the mom has enrollment data for 2019 and the flags for either Jan or Feb are 0, then the case will not meet the enrollment criteria.

But what if the mom was not enrolled in 2019 at all? Since there is no 0 for Jan or Feb, the flag will not be "zeroed out", and it will look like she has 3 months of continuous enrollment as long as she is enrolled in Dec 2018 only. I've updated the example data accordingly. Case 3456 should not qualify for continuous enrollment.

data test;
    input ID deliv_date YEAR_of_BIRTH YEAR_of_ENROLL ENRMON1 ENRMON2 ENRMON3 ENRMON4 ENRMON5 ENRMON6 ENRMON7 ENRMON8 ENRMON9 ENRMON10 ENRMON11 ENRMON12;
    datalines;
    1234    06/01/2017  2017   2017 1 1 1 1 1 1 1 1 1 1 1 1
    1234    06/01/2017  2017   2018 1 1 1 1 1 1 1 1 1 1 1 1
    1234    06/01/2017  2017   2019 1 1 1 1 1 1 1 1 1 1 1 1
    2345    08/15/2018  2018   2017 1 1 1 1 1 1 1 1 1 1 1 1
    2345    08/15/2018  2018   2018 1 1 1 1 1 1 1 1 1 1 1 1
    2345    08/15/2018  2018   2019 0 1 1 1 1 1 1 1 1 1 1 1
    3456    11/10/2018  2018   2017 1 1 1 1 1 1 1 1 1 1 1 1
    3456    11/10/2018  2018   2018 1 1 1 1 1 1 1 1 1 1 1 1
    ;

/* get dates for the enrollments and restructure*/
data useful;
   set test;
   array m (*) enrmon: ;
   do i=1 to dim(m);
      testdate= mdy(i,1,year_of_enroll);
      Enrollcode = m[i];
      output;
   end;
   format testdate mmddyy10.;
   keep id -- YEAR_of_ENROLL testdate enrollcode;
run;

data setflags;
   set useful;
   if (intck('month',deliv_date,testdate) le 12) then Cont_flag = enrollcode;
run;

proc summary data=setflags nway;
   class id  deliv_date YEAR_of_BIRTH;
   var  Cont_flag;
   output out=want (drop=_: ) min= ;
run;



/* get dates for the enrollments and restructure*/
data useful;
   set test;
   array m (*) enrmon: ;
   do i=1 to dim(m);
      testdate= mdy(i,1,year_of_enroll);
      Enrollcode = m[i];
      output;
   end;
   format testdate mmddyy10.;
   keep id -- YEAR_of_ENROLL testdate enrollcode;
run;

data setflags;
   set useful;
   if (intck('month',deliv_date,testdate) le 3) then Cont_flag = enrollcode;
run;

proc summary data=setflags nway;
   class id  deliv_date YEAR_of_BIRTH;
   var  Cont_flag;
   output out=want (drop=_: ) min= ;
run;

[Tom · Accepted solution]

Did you try the ESTRING idea (enrollment string).  That is a character variable that has one character for every month in the time window of interest with symbols like 1 or X to indicate enrollment for that month.  Then to test for continuous enrollment you just need to test if there is anything that is not that character in substring that represents the period you want to check.

So for your example we could use 2017 as the base year and make the string long enough for 3 years.

data enrollment;
  input ID $ YEAR_of_ENROLL (ENRMON1-ENRMON12) (:$1.);
datalines;
1234 2017 1 1 1 1 1 1 1 1 1 1 1 1
1234 2018 1 1 1 1 1 1 1 1 1 1 1 1
1234 2019 1 1 1 1 1 1 1 1 1 1 1 1
2345 2017 1 1 1 1 1 1 1 1 1 1 1 1
2345 2018 1 1 1 1 1 1 1 1 1 1 1 1
2345 2019 0 1 1 1 1 1 1 1 1 1 1 1
3456 2017 1 1 1 1 1 1 1 1 1 1 1 1
3456 2018 1 1 1 1 1 1 1 1 1 1 1 1
;

%let baseyr=2017 ;
%let years=3;
%let months=%eval(12*&years);
data estring;
  do until (last.id);
    set enrollment;
    by id;
    length estring $&months ;
    if first.id then estring=repeat('.',&months-1);
    array enr enrmon1-enrmon12 ;
    do index=1 to 12 ;
      substr(estring,12*(year_of_enroll-&baseyr)+index,1) = enr[index];
    end;
  end;
  keep id estring;
run;

Now just merge that with the event data and check the period of interest.

data delivery;
  input ID $ deliv_date :mmddyy. ;
  format deliv_date yymmdd10.;
datalines;
1234 06/01/2017
2345 08/15/2018
3456 11/10/2018
;
data want;
  merge delivery estring;
  by id ;
  length string3 $3;
  string3 = substr(estring,12*(year(deliv_date)-&baseyr)+month(deliv_date)+1,3);
  enroll3 = 0 = verify(string3,'1');
run;

Result

Obs     ID     deliv_date                  estring                   string3    enroll3

 1     1234    2017-06-01    111111111111111111111111111111111111      111         1
 2     2345    2018-08-15    111111111111111111111111011111111111      111         1
 3     3456    2018-11-10    111111111111111111111111............      1..         0

[Wolverine]

It works exactly as advertised! Thank u!