Hi folks,
I'm trying to create a dummy variable (0,1) whether patient had ever/never enrolled in an insurance in a year of diagnosis? (date_diagnosis)=year(date pairs).
Output variable I want is "MARK" in the mock data.
For example date_diagnosis is 01DEC2004. Insurance range is 01NOV - 31JAN2017. Therefore variable MARK takes value 0 indicating that patient had no insurance in the year of diagnosis.
Thanks in advance. I appreciate your time.
01DEC2004 | 01NOV2005 |
31JAN2017 |
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-Jun-05 31-Jul-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 . . . . . . . . 1
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 . . . . . . . . . . . . 1
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 . . 1
6 15-Sep-05 1-Mar-05 31-Mar-05 1-Feb-06 28-Feb-06 . . . . . . . . . . . . 1
7 17-Oct-05 1-Mar-05 31-May-05 1-Jan-06 28-Feb-06 1-Feb-07 28-Feb-07 . . . . . . . . . . 1
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 1
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 . . 1
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 . . . . . . . . . . . . 1
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
;
proc print; run;
So rather than testing if DATE_DIAGNOSIS is between the BEG/END dates you need to test if the YEAR(DATE_DIAGNOSIS) is between YEAR(BEG) and YEAR(END).
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;
run;
So rather than testing if DATE_DIAGNOSIS is between the BEG/END dates you need to test if the YEAR(DATE_DIAGNOSIS) is between YEAR(BEG) and YEAR(END).
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;
run;
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.