How to check members qualification? YES or NO value

[mrafael03]

Requirements: The user must be active from the date of  discharge through 30 days after the discharge, with no gaps in enrollment.

FIELD NAMES:

IN_DATE 

OUT_DATE "column to identify date of discharge"

ENROLL_DT "column to identify members enrollment date"

/*See Data Set below*/
/*1. ENROLL1 - Members Enrollment*/
/*2. ACTIVITY1 - Member Activity*/

DATA ENROLL;
INPUT ID $1-3 ENROLL_DT 4-12;
DATALINES;
001 20180101
001 20180201
001 20180301
001 20180401
003 20180201
003 20180301
003 20180401
;
RUN;

DATA ENROLL1;
SET ENROLL;
ENROLL_DT=INPUT(PUT(ENROLL_DT,8.),YYMMDD8.);
FORMAT ENROLL_DT MMDDYY10.;
RUN;

PROC SORT DATA=ENROLL1;
BY ID ENROLL_DT;
RUN;




DATA ACTIVITY;
INPUT ID $1-3 IN_DATE 4-12 OUT_DATE 13-21;
DATALINES;
001 20171230 20180102
001 20180316 20180324
002 20180225 20180227
003 20180201 20180215
003 20180408 20180424
;
RUN;

DATA ACTIVITY1;
SET ACTIVITY;
IN_DATE=INPUT(PUT(IN_DATE,8.),YYMMDD8.);
OUT_DATE=INPUT(PUT(OUT_DATE,8.),YYMMDD8.);
FORMAT IN_DATE OUT_DATE MMDDYY10.;
RUN;

PROC SORT DATA=ACTIVITY1;
BY ID IN_DATE OUT_DATE;
RUN;

Sample Scenario:

ID 001 has an OUT_DATE of 1/2/2018 "SEE DATA SET ACTIVITY1" and user were active on month of 1/1/2018 to 2/1/2018 1/2/2018 "SEE DATA SET ENROLL1". Therefore, the user is qualified.

Expected Result

ID	IN_DATE	OUT_DATE	ELIG ON OUT_DATE	ELIG ON 30 DAYS AFTER OUT_DATE	QUALIFIED
001	12/30/2017	1/2/2018	YES	YES	YES
001	3/16/2018	3/24/2018	YES	YES	YES
002	2/25/2018	2/27/2018	NO	NO	NO
003	2/1/2018	2/15/2018	YES	YES	YES
003	4/8/2018	4/24/2018	YES	NO	NO

[mklangley]

Shouldn't the 2nd line of your output (ID = 001, OUT_DATE = 3/24/2018) be NO for eligible 30 days after OUT_DATE?

 proc sql;
    create table enroll_range as
    select id
          ,min(enroll_dt) as min_enroll_dt format MMDDYY10.
          ,max(enroll_dt) as max_enroll_dt format MMDDYY10.
    from enroll1
    group by id;
quit;

proc sql;
    create table want as
    select a.id
          ,a.in_date
          ,a.out_date
          ,case
               when e.min_enroll_dt <= a.out_date <= max_enroll_dt
                   then 'YES'
               else 'NO'
           end as ELIG_ON_OUT_DATE
          ,case
               when e.min_enroll_dt <= intnx('day', a.out_date, 30, 's') <= max_enroll_dt
                   then 'YES'
               else 'NO'
           end as ELIG_ON_30_DAYS_AFTER_OUT_DATE
          ,case
               when calculated elig_on_out_date = 'YES' and calculated elig_on_30_days_after_out_date = 'YES'
                   then 'YES'
               else 'NO'
           end as QUALIFIED
    from activity1 a
    left join enroll_range e
        on a.id = e.id;
quit;

proc print; run;

 

[mrafael03]

@mklangley 

Members eligibility always starts with the first day of the month so regardless if their OUT_DATE happened on 3/24 the member considered as eligible for MARCH with continuous enrollment until APRIL.

So basically the count starts at the beginning of the month

ID	IN_DATE	OUT_DATE	ELIG_ON_DISCHARGE	ELIG_30 DAYS AFTER DISCHARGED	QUALIFIED
001	3/16/2018	3/24/2018	3/1/2018 - MARCH ACTIVE	YES - APRIL ACTIVE	YES

[mrafael03]

@mklangley 

Let me rephrase so basically if the member has enrolled date of 1/1/2018 that considered the whole month of March "3/1/2018-3/31/2018" and continuously active until April "4/1/2018-4/30/2018"

OUT_DATE - 3/24/2018

AFTER 30 DAYS - April 23, 2018

Therefore, member is eligible

[mklangley · Accepted solution]

Got it--thank you for the explanation. I modified the code accordingly:

proc sql;
    create table enroll_range as
    select id
          ,min(enroll_dt) as min_enroll_dt format MMDDYY10.
          ,max(enroll_dt) as max_enroll_dt format MMDDYY10.
    from enroll1
    group by id;
quit;

proc sql;
    create table want as
    select a.id
          ,a.in_date
          ,a.out_date
          ,case
               when e.min_enroll_dt <= a.out_date <= intnx('month', e.max_enroll_dt, 0, 'e')
                   then 'YES'
               else 'NO'
           end as ELIG_ON_OUT_DATE
          ,case
               when intnx('day', a.out_date, 30) <= intnx('month', e.max_enroll_dt, 0, 'e')  
                   then 'YES'
               else 'NO'
           end as ELIG_ON_30_DAYS_AFTER_OUT_DATE
          ,case
               when calculated elig_on_out_date = 'YES' and calculated elig_on_30_days_after_out_date = 'YES'
                   then 'YES'
               else 'NO'
           end as QUALIFIED
    from activity1 a
    left join enroll_range e
        on a.id = e.id;
quit;

proc print; run;

Output: