Continuous Enrollment Count

[jmmedina25]

Hello, I need to count the months of continuous enrollment per member.  When there are gaps in enrollment the count must reset for the member.

Here is the data format:

memberid  enrollment

111               202201

111               202202

111               202205

222              202205

222              202206

Here's the output I'm looking for:

memberid  enrollment    cont_enroll_count

111               202201            2

111               202202            2

111               202205            1

222              202205             2

222              202206             2

Also, if there's another way to do this, please let me know!

[PeterClemmensen]

Is enrollment an actual SAS Date value or simply the integer as we see it: 202201 ?

[jmmedina25]

the enrollment date is in numeric format

[PeterClemmensen]

What format is applied to it?

[jmmedina25]

Numeric format 11.

[PeterClemmensen]

Then you may have to change the date values into Actual SAS Dates for the posted code to work.

[PeterClemmensen]

Try this

data have;
input memberid enrollment : yymmn6.;
format enrollment yymmn6.;
datalines;
111 202201 
111 202202 
111 202205 
222 202205 
222 202206 
;

data temp;
   set have;
   by memberid;

   lag = ifn(first.memberid, ., lag(enrollment));
   if first.memberid | intck('month', lag, enrollment) > 1 then c = 1;
   else c + 1;

   if c = 1 then g + 1;
run;

proc sql;
   create table want as
   select memberid
        , enrollment
        , max(c) as cont_enroll_count
   from temp
   group by g
   ;
quit;

Result:

memberid enrollment cont_enroll_count
111      202201     2
111      202202     2
111      202205     1
222      202206     2
222      202205     2

[jmmedina25]

Thank you, this did work however, some members have eligibility that spans multiple years and when the function hits a new year the count resets. Any Idea for how to fix this?

This is the example:
Mbr_ID enrollment lag c g
1 202111 . 1 1
1 202112 202111 2 1
1 202201 202112 1 2
1 202202 202201 2 2
1 202203 202202 3 2
1 202204 202203 4 2
1 202205 202204 5 2
1 202206 202205 6 2
1 202207 202206 7 2
1 202208 202207 8 2

[PeterClemmensen]

That is why you have to change the date values into Actual SAS Dates. Then you will not have the problem.

[s_lassen]

Another possibility is to just code for the year ends. If you have data like this:

data have;
input memberid enrollment;
datalines;
111 202201 
111 202202 
111 202205 
222 202205 
222 202206 
333 202112
333 202201
;run;

You can first create the groups of continued enrollment like this (89 is just the difference between e.g. 202201 and 202112, the end of the year):

data groups;
  set have;
  by memberid;
  dif=dif(enrollment);
  if first.memberid then
    group=1;
  else if dif not in(1,89) then group+1;
  drop dif;
run;

And then use that to get the counts:

data want;
  do cont_enroll_count=1 by 1 until(last.group);
    set groups;
	by memberid group;
	end;
  do until(last.group);
    set groups;
	by memberid group;
	output;
	end;
run;

Of course, with a date format solution (as suggested by @PeterClemmensen)  you also check if the months entered are valid. If they are not, the results from the method shown here may get "interesting" (e.g. if you have 202113 followed by 202202, it will just count as continued enrollment, rather than giving an error).