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 |
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:
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;
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 |
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
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:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.