topic Re: Check if month and year fall in any of date pairs? in SAS Programming

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

Cruise — Fri, 03 May 2019 14:59:50 GMT

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-values/m-p/554422#M154223

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;

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

ballardw — Fri, 03 May 2019 15:05:39 GMT

@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:

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-values/m-p/554422#M154223

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.

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

Cruise — Fri, 03 May 2019 15:09:37 GMT

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.

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

Tom — Fri, 03 May 2019 15:10:50 GMT

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')

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

Cruise — Fri, 03 May 2019 15:12:16 GMT

Thanks Tom! Great solution! Thank you very much.