Calculate Continuous Enrollment 12 months before and after reference date

Question

I am new to claims data and relatively new to SQL...and would greatly appreciate your help.

I have data similar to this:

Customer    Group   Member  ServiceDt
ABCDE       123     1111    01-Jan-12
            123     1111    01-Feb-12
            123     1111    01-Mar-12
            123     1112    01-Feb-12
            123     1112    01-Mar-12
FGHIJ       456     1116    01-Feb-12
KLMNO       567     1117    01-Mar-12
            678     1118    01-Feb-12
            789     1119    01-Mar-12

A member can have one entry per month to indicate active enrollment date. So, a member can have 24 rows for two years' worth of enrollment. Another could have just five rows and so on...Each member is a part of a group who in turn are a part of a customer. (A customer could have multiple groups - with many members in each group and each member has a row per month).

I need to evaluate continuous enrollment from the service date going back 12 months and then going ahead 12 months ( an year of enrollment before and after the service date) and then roll it up to group by CUSTOMER and GROUP numbers.

I have tried intck/intcx functions but seem to be doing something wrong. When I try the first. and last. way, I am getting lost. This data is so new to me.:)

Here are snippets of the code (not including the server connection stuff) I tried so far:

beg1ylag = intnx('month',&eff ,-12,'m');
add1yfut = intnx('month',&eff , 12,'m');

If first.member_id then do;
    mth_cnt     = 0;
    lag_mth_cnt = 0;
    fut_mth_cn  = 0;
End;

 mth_cnt+1;
  if eff_dt < servicedt then lag_mth_cnt+1;
  if eff_dt > servicedt then fut_mth_cnt+1;

  if last.member_id then output;


Proc SQL;
Select (x,y,z

 beg1ylag = intnx('month',servicedt,-12,'m');
 add1yfut = intnx('month',servicedt, 12,'m');

call symput('_beg1ylag',cats("'",year(beg1ylag),'-',put(month(beg1ylag),z2.),"Middle'"));
call symput('_add1yfut',cats("'",year(add1yfut),'-',put(month(add1yfut),z2.),"Middle'"));
Quit;

The result I get is empty rows. I don't know what I am doing or doing wrong....please guide me. Also, please note that the serviced is NOT fixed. Each member can have a different one - as you can see above. Ultimately, I need to be able to pick members and group them by their group number if they have had 24 months of continuous membership around the servicedt.

Answer 1

If I interpret your question well, you need

%let eff = '01Feb2012'd;
%let span = 12;

Title "Members enrolled continuously from &span months before &eff till &span months after &eff";
proc sql;
    select Customer, Group, Member
    from   enrol
    where  ServiceDt between intnx('month',&eff ,-&span,'b') and intnx('month',&eff ,+&span,'b')
    group by Customer, Group, Member
    having count(*) EQ 1 + INTCK('month', intnx('month',&eff ,-&span,'b'), intnx('month',&eff ,+&span,'b'));
quit;

Note that you should use intnx with 'b' for beginning, not 'm' for middle as your data mentions begin of month dates too.

Answer 2

If your source data is in a SAS dataset and you can run SAS code on the server where the data resides, the following is extremely efficient.

%let span = 12;

Data Continuous (keep=Customer Group Member eff);
    set Service;
    by Customer Group Member ServiceDt;

** For each new member or interuption of service start a new Continuous service interval **;

    retain From;
    if first.Member or dif(ServiceDt) GT 31 
        then From = ServiceDt;

** If service started at least 2 span months ago then span months ago is the centre of a sufficiently long continuous service interval **;

    if INTCK('month', From, ServiceDt) GE %eval(2 * &span) then do;
        eff = intnx('month', ServiceDt,-&span,'b');
        output;
    end;
run;

It gives your results for every possible service interval centre (=eff) at once.