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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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