BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrafael03
Obsidian | Level 7

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

IDIN_DATEOUT_DATEELIG ON OUT_DATEELIG ON 30 DAYS AFTER OUT_DATEQUALIFIED
00112/30/20171/2/2018YESYESYES
0013/16/20183/24/2018YESYESYES
0022/25/20182/27/2018NONONO
0032/1/20182/15/2018YESYESYES
0034/8/20184/24/2018YESNONO

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mklangley
Lapis Lazuli | Level 10

 

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:

 

mklangley_1-1594834970769.png

View solution in original post

4 REPLIES 4
mklangley
Lapis Lazuli | Level 10

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;

 Capture.PNG

 

 

mrafael03
Obsidian | Level 7
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
 
IDIN_DATEOUT_DATEELIG_ON_DISCHARGEELIG_30 DAYS AFTER DISCHARGEDQUALIFIED
0013/16/20183/24/20183/1/2018 - MARCH ACTIVEYES - APRIL ACTIVEYES
 
 
mrafael03
Obsidian | Level 7

@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
Lapis Lazuli | Level 10

 

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:

 

mklangley_1-1594834970769.png

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1489 views
  • 0 likes
  • 2 in conversation