I have code below that produces a dataset where we find when the ID enrolls and disenrolls from a program . the code below is picking up the first two enrollments but is doesnt pick up the any other enrollments. For example ID attached in the excel the rEnroll_date should be 04/01/20222 but its only picking up the first reenrollment in 01/01/2021. also i how do i add DTGStart3 and DTGEND3 where for this ID is would be
DTGSTART1 DTGEND1 DTGSTART2 DTGEND2 DTGSTART3 DTGEND3
Jan-19 Sep-19 Jan-20 Dec-21 Apr-22 Dec-00
*Dec-00 = Dec-4000
Ive trying adding other do loops but they arent working.
data _null_;
*convert current pricing file indicator into SASdate;
pricedate = MDY(substr("&curr_price_mo.",7,2),1,substr("&curr_price_mo.",4,2)+2000);
call symput('pricedate_yymmdd',put(pricedate,yymmdd6.));
*# months from Dec 2014 to current price file (need +1 to include Dec 2014 in month count);
currmons = intck('month',"01jul2019"d,pricedate) +1;
call symput('pricemos',currmons);
run;
DATA mem_qip07 (drop= h i j k );
SET mem_qip06_1;
format DTGSTART1 DTGEND1 DTGSTART2 DTGEND2 FIRST_MO initial_disenroll2 MMYYS.;
format temp_disENROLL_DATE temp_RENROLL_DATE temp_nENROLL_DATE
nENROLL_DATE rENROLL_DATE disENROLL_DATE
first_mo MMDDYY10.;
length temp_dis_varname temp_renr_varname temp_nenr_varname $8;
*Create array of all date variables;
ARRAY MOS(*) MON19_01-MON19_12 MON20_01-MON20_12 MON21_01-MON21_12 MON22_01-&curr_price_mo.;
*Step 1: Assigns FINAL enrollment date to disenroll2, equal to current month for current members;
do i= 1 to dim(MOS);
if MOS{i} ne 0 then disenroll2 = vname(MOS{i});
end;
drop i;
*Step 2: Assigns FIRST enrollment date for patient starting 2018 to current;
initial_enroll_flag=0;
do j =1 to dim(MOS);
IF initial_enroll_flag=0 THEN DO;
IF MOS{j} = 1 THEN DO;
initial_enroll_flag=1;
enroll1=vname(MOS{j});
initial_enroll_cntpls=j+1;
initial_enroll_cnt=j;
END;
END;
END; *<-- ends the "do j=1 to ... " do-loop;
*Step 3: Assigns FIRST dis-enrollment date for patient starting 2018 to current;
initial_disenroll_flag=0;
do j =1 to dim(MOS)-1; *<-- # months (global macro var) minus 1;
IF initial_disenroll_flag=0 THEN DO;
IF MOS{j} = 1 and MOS{j+1} = 0 THEN DO;
initial_disenroll_flag=1;
disenroll1=vname(MOS{j});
initial_disenroll_cntpls=j+1;
initial_disenroll_cnt=j;
END;
END;
END; *<-- ends the "do j=1 to ... " do-loop;
*Step 4: find SECOND time patient is enrolled following FIRST dis-enrollment (above);
initial_2reenroll_flag=0;
if initial_disenroll_flag=1 then DO j = initial_disenroll_cnt TO dim(MOS)-1 ;
IF initial_2reenroll_flag=0 THEN DO;
IF MOS{j} = 0 and MOS{j+1} = 1 THEN DO;
initial_2reenroll_flag=1;
enroll2=vname(MOS{j+1});
initial_2reenroll_cntpls=j+1;
initial_2reenroll_cnt=j;
END;
END;
END; *<-- ends the "do j=1 to ... " do-loop;
*Step 5: find FIRST time patient is not enrolled after being enrolled starting in 2015 to current;
temp_disENR=0;
do j =1 to dim(MOS)-1;
/* do j =1 to (&pricemos. - 1); */
*<-- # months (global macro var) minus 1;
IF temp_disENR=0 THEN DO;
IF MOS{j} = 1 and MOS{j+1} = 0 THEN DO;
temp_disENR=1;
temp_dis_varname=vname(MOS{j});
temp_dis_arraynum=j+1;
END;
END;
END; *<-- ends the "do j=1 to ... " do-loop;
*Step 6: find last time patient is enrolled (re-enrolled) after first time disenrolled (from above) starting in 2015 to current;
temp_RENR=0;
* I = &pricemos. <-- global macro var for # months in array;
*go backwards from current month back to month patient disenrolled;
/* if temp_disENR=1 then DO I = &pricemos. TO (temp_dis_arraynum) BY -1; */
if temp_disENR=1 then DO j = initial_disenroll_cnt TO dim(MOS)-1 ;
IF temp_RENR=0 THEN DO;
IF MOS{j} = 0 and MOS{j+1} = 1 THEN DO;
temp_RENR=1;
temp_renr_varname=vname(MOS{j+1});
*temp_renr_arraynum=I;
END;
END;
END; *<-- ends the "do I=14 to ... " do-loop;
*Step 7a: find if patient looks like is newly enrolled starting in 2015 to current;
temp_nENR=0;
*Start with Jan 2015 in array: "do k=2"
will check on data from earlier years later in program;
DO k = 2 TO (&pricemos. - 1); *<-- # months (global macro var) minus 1;
IF temp_nENR=0 THEN DO;
IF MOS{k} = 0 AND MOS{k+1}=1 THEN DO;
temp_nENR=1;
temp_nenr_varname=vname(MOS{k+1});
*temp_nenr_arraynum=k+1;
END;
END;
END; *<-- ends the "do k=2 to ... " do-loop;
*for months after January;
contin_enr19 = MIN(OF MON19_01-MON19_12);
*enrolled all months of 2020;
*for months after January;
contin_enr20 = MIN(OF MON20_01-MON20_12);
*enrolled all months of 2021;
*for months after January;
contin_enr21 = MIN(OF MON21_01-MON21_12);
*for months after January;
contin_enr22 = MIN(OF MON22_01-&curr_price_mo.);
*enrolled any month of 2018;
*for months after January;
/* ever_enr18 = Max(OF MON18_01-MON18_12);*/
*enrolled any month of 2019;
*----------------->; *for January only;
*ever_enr19 = mon19_01;
*----------------->; *for January only;
ever_enr19 = Max(OF MON19_01-MON19_12);
*enrolled any month of 2020;
*----------------->; *for January only;
/* ever_enr20 = mon20_01;*/
*----------------->; *for January only;
ever_enr20 = Max(OF MON20_01-MON20_12);
*enrolled any month of 2021;
*----------------->; *for January only;
/* ever_enr21 = mon21_01;*/
ever_enr21 = Max(OF MON21_01-MON21_12);
*enrolled any month of 2020;
*----------------->; *for January only;
/* ever_enr22 = mon22_01;*/
ever_enr22 = Max(OF MON22_01-&curr_price_mo.);
*Step 9: First pricing file the patient appears in;
array fm(19:22,1:12)
mon19_01-mon19_12
mon20_01-mon20_12
mon21_01-mon21_12
mon22_01-mon22_12
;
FIRST_MO=.;
do p=19 to 22;
do q=1 to 12;
if FIRST_MO=. then do;
if fm{p,q}=1 then FIRST_MO=mdy(q,1,2000+p);
end;
end;
end;
drop p q;
*Step 10: Format enrollment /re-enrollment date variables;
DTGSTART1 = MDY(substr(enroll1,7,2),1,substr(enroll1,4,2)+2000);
DTGEND1 = MDY(substr(disenroll1,7,2),1,substr(disenroll1,4,2)+2000);
DTGSTART2 = MDY(substr(enroll2,7,2),1,substr(enroll2,4,2)+2000);
DTGEND2 = MDY(substr(disenroll2,7,2),1,substr(disenroll2,4,2)+2000);
temp_disENROLL_DATE = MDY(substr(temp_dis_varname,7,2),1,substr(temp_dis_varname,4,2)+2000);
temp_RENROLL_DATE = MDY(substr(temp_renr_varname,7,2),1,substr(temp_renr_varname,4,2)+2000);
temp_nENROLL_DATE = MDY(substr(temp_nenr_varname,7,2),1,substr(temp_nenr_varname,4,2)+2000);
*If look like a new enrollee from Step 3a/3b AND no evidence of past enrollment, then confirmed they are a new enrollee;
if ( Max(OF ever_enr8-ever_enr14) ne 1) and (temp_nENR eq 1) then do;
new_enr=1;
nENROLL_DATE=temp_nENROLL_DATE;
end;
*Otherwise if enrolled in a month prior to 2015 then NOT a new enrollee.
This means they were enrolled *and* disenrolled prior to 2015, but RE-enrolled 2015 to current
In other words, there final status is re-enrolled, not new enrolled.
;
else if (temp_nENR eq 1) then do;
new_enr=0;
nENROLL_DATE=.;
re_ENR=1;
rENROLL_DATE = max(temp_renroll_date,temp_nENROLL_DATE);
end;
*else they were never a possible new enrollee to begin with so set their final new enrollee values to "No";
*coding as above;
else if (temp_nENR eq 0) then do;
new_enr=0;
nENROLL_DATE=.;
end;
*Step 11: Final variable clean-up of temp_ vs. perm vars for dis-/re-enroll variables not incuded in Step 4;
*If not already flagged as a re-enrollment situation, assign according to temp variables;
if re_enr=. then re_enr=temp_RENR;
if rENROLL_DATE=. then RENROLL_DATE=temp_RENROLL_DATE;
*For parallel with re-enroll and new variables;
dis_enr=temp_disENR;
disENROLL_DATE=temp_disENROLL_DATE;
*Indicator flags for additional QC checks;
if (nENROLL_DATE ne .) then do;
if (dtgp gt nENROLL_DATE) then dtgp_after_new=1;
else if (dtgp eq nENROLL_DATE) then dtgp_same_new=1;
else if (dtgp lt nENROLL_DATE) then dtgp_before_new=1;
end;
if (RENROLL_DATE ne .) and (dtgp gt RENROLL_DATE) then dtgp_after_re=1;
if (disENROLL_DATE ne .) and (dtgp gt disENROLL_DATE) then dtgp_after_dis=1;
*Step 12: Reassign final dates ;
if DTGEND1 = DTGEND2 then DTGEND2 = .;
if DTGEND1 = . then DTGEND1 = DTGEND2;
if DTGEND1 = DTGEND2 then DTGEND2 = .;
if DTGEND1 = "&curr_month"d then DTGEND1 = '31DEC4000'd;
if DTGEND2 = "&curr_month"d then DTGEND2 = '31DEC4000'd;
run;
Click image to register for webinar
