Check if month and year fall in any of date pairs?

[Cruise]

Hi folks,

I'm trying to check whether patient was enrolled in an insurance in the same month and the year of diagnosis?

Desired output variable is "MARK".

How this mark variables is created is for example:

1st row. 29MAR2005 falls in range: 01MAR2005 - 31MAR2005 hence, mark=1;
2nd row. 08JUN2005 no overlap, hence mark=0;
3rd row. 24JUN2005 no overlap, hence mark=0;
4th row. 18JUL2005 missed the date range: 01AUG2005 31DEC2006, hence mark=0; 

The current code outputs enrolled=1 if patient was enrolled in the same year of diagnosis. But not considering month yet.

https://communities.sas.com/t5/SAS-Programming/Check-if-fixed-value-is-included-in-the-range-of-valu...

DATA HAVE;
format id 8. DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 date9. MARK 1.; 
informat DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 date9. MARK 1.; 
input ID DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 MARK; 
cards;
1	29-Mar-05	1-Nov-03	30-Nov-03	1-Jan-04	31-Jul-04	1-Oct-04	31-Dec-04	1-Mar-05	31-Mar-05	1-Oct-05	31-Jan-06	1-Feb-11	30-Jun-11	1-Jan-14	28-Feb-14	1-Jan-15	31-Jan-17	1
2	8-Jun-05	1-Jan-02	31-Jan-02	1-Dec-05	28-Feb-13	1-Dec-16	31-Dec-16	1-Oct-17	30-Nov-17	.	.	.	.	.	.	.	.	0
3	24-Jun-05	1-Jan-02	31-Jan-02	1-Aug-02	30-Sep-02	1-Nov-04	31-Dec-04	1-Jul-11	31-Aug-11	.	.	.	.	.	.	.	.	0
4	18-Jul-05	1-Aug-05	31-Dec-06	1-Dec-09	31-Dec-10	.	.	.	.	.	.	.	.	.	.	.	.	0
5	15-Aug-05	1-May-02	31-May-02	1-Mar-03	31-May-03	1-Oct-03	30-Nov-03	1-Jan-04	29-Feb-04	1-Apr-04	30-Apr-04	1-Apr-06	31-May-06	1-Oct-06	30-Nov-06	.	.	0
6	15-Sep-05	1-Mar-05	31-Mar-05	1-Feb-06	28-Feb-06	.	.	.	.	.	.	.	.	.	.	.	.	0
7	17-Oct-05	1-Mar-05	31-May-05	1-Jan-06	28-Feb-06	1-Feb-07	28-Feb-07	.	.	.	.	.	.	.	.	.	.	0
8	30-Oct-05	1-Jun-05	30-Jun-05	1-Aug-05	30-Sep-05	1-May-06	30-Jun-06	1-Nov-09	31-Dec-09	1-May-11	30-Nov-11	1-Jan-13	28-Feb-13	1-Dec-13	30-Nov-14	1-Mar-15	31-May-15	0
9	10-Nov-05	1-Jun-04	30-Jun-04	1-Sep-04	31-Oct-04	1-May-05	30-Jun-05	1-Sep-06	30-Nov-06	1-Feb-07	30-Nov-07	1-Nov-08	31-Dec-08	1-Apr-09	30-Apr-09	.	.	0
10	17-Nov-18	1-May-05	31-Jul-05	1-Apr-06	31-May-06	1-Apr-09	31-May-09	1-Apr-10	30-Apr-10	1-Apr-11	30-Jun-11	1-Mar-12	30-Apr-12	1-Mar-13	31-Mar-13	1-Dec-16	31-Mar-17	0
11	10-Dec-04	1-Nov-04	30-Jun-08	1-Oct-08	30-Jun-11	.	.	.	.	.	.	.	.	.	.	.	.	0
12	14-Dec-04	1-Nov-04	31-Oct-08	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1
13	1-Dec-08	1-Nov-04	31-Oct-08	1-Dec-08	31-Dec-16	.	.	.	.	.	.	.	.	.	.	.	.	1
14	24-Dec-04	1-Nov-04	30-Jun-05	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1
15	1-Dec-04	1-Nov-05	31-Jan-17	.	.	.	.	.	.	.	.	.	.	.	.	.	.	0
16	22-Dec-05	1-Nov-05	31-Dec-16	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1
17	3-Dec-09	1-Nov-09	31-Dec-16	1-Nov-17	31-Dec-17	.	.	.	.	.	.	.	.	.	.	.	.	1
18	1-Dec-10	1-Nov-10	31-Jul-11	1-Nov-13	31-Oct-14	.	.	.	.	.	.	.	.	.	.	.	.	1
;

data want;
  set have;
  array e_beg elig_beg: ;
  array e_end elig_end: ;
  do i=1 to dim(e_beg) until(enrolled);
    enrolled = year(e_beg[i]) <= year(date_diagnosis) <= year(e_end[i]);
  end;
  drop i; 
run;

proc print; run; 

[ballardw]

---

@Cruise wrote:

Hi folks,

I'm trying to check whether patient was enrolled in an insurance in the same month and the year of diagnosis?

Desired output variable is "MARK".

How this mark variables is created is for example:

1st row. 29MAR2005 falls in range: 01MAR2005 - 31MAR2005 hence, mark=1;
2nd row. 08JUN2005 no overlap, hence mark=0;
3rd row. 24JUN2005 no overlap, hence mark=0;
4th row. 18JUL2005 missed the date range: 01AUG2005 31DEC2006, hence mark=0; 

The current code outputs enrolled=1 if patient was enrolled in the same year of diagnosis. But not considering month yet.

https://communities.sas.com/t5/SAS-Programming/Check-if-fixed-value-is-included-in-the-range-of-valu...

DATA HAVE;
format id 8. DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 date9. MARK 1.; 
informat DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 date9. MARK 1.; 
input ID DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 MARK; 
cards;
1	29-Mar-05	1-Nov-03	30-Nov-03	1-Jan-04	31-Jul-04	1-Oct-04	31-Dec-04	1-Mar-05	31-Mar-05	1-Oct-05	31-Jan-06	1-Feb-11	30-Jun-11	1-Jan-14	28-Feb-14	1-Jan-15	31-Jan-17	1
2	8-Jun-05	1-Jan-02	31-Jan-02	1-Dec-05	28-Feb-13	1-Dec-16	31-Dec-16	1-Oct-17	30-Nov-17	.	.	.	.	.	.	.	.	0
3	24-Jun-05	1-Jan-02	31-Jan-02	1-Aug-02	30-Sep-02	1-Nov-04	31-Dec-04	1-Jul-11	31-Aug-11	.	.	.	.	.	.	.	.	0
4	18-Jul-05	1-Aug-05	31-Dec-06	1-Dec-09	31-Dec-10	.	.	.	.	.	.	.	.	.	.	.	.	0
5	15-Aug-05	1-May-02	31-May-02	1-Mar-03	31-May-03	1-Oct-03	30-Nov-03	1-Jan-04	29-Feb-04	1-Apr-04	30-Apr-04	1-Apr-06	31-May-06	1-Oct-06	30-Nov-06	.	.	0
6	15-Sep-05	1-Mar-05	31-Mar-05	1-Feb-06	28-Feb-06	.	.	.	.	.	.	.	.	.	.	.	.	0
7	17-Oct-05	1-Mar-05	31-May-05	1-Jan-06	28-Feb-06	1-Feb-07	28-Feb-07	.	.	.	.	.	.	.	.	.	.	0
8	30-Oct-05	1-Jun-05	30-Jun-05	1-Aug-05	30-Sep-05	1-May-06	30-Jun-06	1-Nov-09	31-Dec-09	1-May-11	30-Nov-11	1-Jan-13	28-Feb-13	1-Dec-13	30-Nov-14	1-Mar-15	31-May-15	0
9	10-Nov-05	1-Jun-04	30-Jun-04	1-Sep-04	31-Oct-04	1-May-05	30-Jun-05	1-Sep-06	30-Nov-06	1-Feb-07	30-Nov-07	1-Nov-08	31-Dec-08	1-Apr-09	30-Apr-09	.	.	0
10	17-Nov-18	1-May-05	31-Jul-05	1-Apr-06	31-May-06	1-Apr-09	31-May-09	1-Apr-10	30-Apr-10	1-Apr-11	30-Jun-11	1-Mar-12	30-Apr-12	1-Mar-13	31-Mar-13	1-Dec-16	31-Mar-17	0
11	10-Dec-04	1-Nov-04	30-Jun-08	1-Oct-08	30-Jun-11	.	.	.	.	.	.	.	.	.	.	.	.	0
12	14-Dec-04	1-Nov-04	31-Oct-08	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1
13	1-Dec-08	1-Nov-04	31-Oct-08	1-Dec-08	31-Dec-16	.	.	.	.	.	.	.	.	.	.	.	.	1
14	24-Dec-04	1-Nov-04	30-Jun-05	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1
15	1-Dec-04	1-Nov-05	31-Jan-17	.	.	.	.	.	.	.	.	.	.	.	.	.	.	0
16	22-Dec-05	1-Nov-05	31-Dec-16	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1
17	3-Dec-09	1-Nov-09	31-Dec-16	1-Nov-17	31-Dec-17	.	.	.	.	.	.	.	.	.	.	.	.	1
18	1-Dec-10	1-Nov-10	31-Jul-11	1-Nov-13	31-Oct-14	.	.	.	.	.	.	.	.	.	.	.	.	1
;

data want;
  set have;
  array e_beg elig_beg: ;
  array e_end elig_end: ;
  do i=1 to dim(e_beg) until(enrolled);
    enrolled = year(e_beg[i]) <= year(date_diagnosis) <= year(e_end[i]);
  end;
  drop i; 
run;

proc print; run; 

---

If you don't want just the YEAR compared then why are you using the YEAR function?

Without any functions applied to the dates then the comparison is the date_diagnosis between the other two dates.

[Cruise]

Because, current code intends to check whether patients were enrolled in an insurance in the same year that disease was diagnosed. But I wonder whether patient had insurance in the month and year of diagnoses was made. Patients lose their enrollment month to month.

[Tom · Accepted solution]

Instead of YEAR() function use the INTNX() function.

enrolled=intnx('month',e_beg[i],0,'b')<=date_diagnosis<=intnx('month',e_end[i],0,'e')

[Cruise]

Thanks Tom! Great solution! Thank you very much.