topic Re: Continuous Enrollment Count in SAS Programming

Continuous Enrollment Count

jmmedina25 — Mon, 21 Nov 2022 19:34:25 GMT

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!

Re: Continuous Enrollment Count

PeterClemmensen — Mon, 21 Nov 2022 19:42:19 GMT

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

Re: Continuous Enrollment Count

jmmedina25 — Mon, 21 Nov 2022 19:46:23 GMT

the enrollment date is in numeric format

Re: Continuous Enrollment Count

PeterClemmensen — Mon, 21 Nov 2022 19:48:00 GMT

What format is applied to it?

Re: Continuous Enrollment Count

PeterClemmensen — Mon, 21 Nov 2022 20:07:12 GMT

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

Re: Continuous Enrollment Count

jmmedina25 — Mon, 21 Nov 2022 20:14:42 GMT

Numeric format 11.

Re: Continuous Enrollment Count

PeterClemmensen — Mon, 21 Nov 2022 20:19:01 GMT

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

Re: Continuous Enrollment Count

jmmedina25 — Wed, 23 Nov 2022 01:41:22 GMT

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

Re: Continuous Enrollment Count

PeterClemmensen — Wed, 23 Nov 2022 05:45:10 GMT

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

Re: Continuous Enrollment Count

s_lassen — Wed, 23 Nov 2022 11:50:01 GMT

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).