Calculate how many continuous enrollment months

Question

I have a data set in which I must pull out the observations that have been enrolled to calculate how many continuous months. See an example of what the data looks like below.

data test;
    input transMonths MemberID Plan $;
    datalines;
    201510  00001   HMO
    201601  00001   HMO
    201602  00001   HMO
    201603  00001   PPO
    201604  00001   HMO
    201605  00001   HMO
    201606  00001   HMO
;

The main question: how do I get SAS to read transMonths and calculate how many continuous months with Plan_HMO per memberID has?

In the above example, memberID 00001 has only 3 continuous months from 201604 to 201606. I just need to calculate the most recent continuous months.

Any help is appreciated!

Answer 1

You can use group processing with notsorted flag.

data result;
    retain transMonths_first;
    set test;
    by plan notsorted;

    if first.plan then do;
        months = 0;
        transMonths_first = transMonths;
    end;

    months + 1;

    if last.plan then do;
        output;
    end;
run;

Answer 2

Use of Lag is probably a good way of getting the data you need:

data test;
    input transMonths MemberID Plan $;
    datalines;
    201510  00001   HMO
    201601  00001   HMO
    201602  00001   HMO
    201603  00001   PPO
    201604  00001   HMO
    201605  00001   HMO
    201606  00001   HMO
;

*Sort data with ID, months, Plan;
proc sort data = test;
by MemberID transMonths Plan;

* For each member, compare the month and Plan to previous observation and add the number of continuous months if they are the same plan but only one month ahead;
data want;
set test;
by MemberID Plan notsorted;
retain continuous_months;
lag_month=lag(transMonths);
lag_plan=lag(plan);

if first.MemberID or first.Plan then continuous_months=1;
else do;
    if Plan = lag_plan and transMonths = lag_month +1 then continuous_months+1;
end;
run;

*Get the member ID and maximum continuous months for all HMOs only;
proc sql;
create table want1 as
select MemberID,max(continuous_months) as max_continuous_months
from want
where Plan ='HMO'
group by 1;
quit;