topic Re: How to check members qualification? YES or NO value in SAS Programming

How to check members qualification? YES or NO value

mrafael03 — Wed, 15 Jul 2020 15:23:00 GMT

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

Re: How to check members qualification? YES or NO value

mklangley — Wed, 15 Jul 2020 16:52:01 GMT

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;

Re: How to check members qualification? YES or NO value

mrafael03 — Wed, 15 Jul 2020 17:17:54 GMT

@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

Re: How to check members qualification? YES or NO value

mrafael03 — Wed, 15 Jul 2020 17:29:45 GMT

@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

Re: How to check members qualification? YES or NO value

mklangley — Wed, 15 Jul 2020 17:43:28 GMT

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: