Calculate age from dates based on a condition

[amarikow57]

My dataset has multiple visit dates for a given patient (in long format). Let's say:

PATIENT_ID  VIST  VISIT_DATE  DOB             DIAGNOSIS

1                     1        01/12/2011    02/15/1997  1

1                     2        02/17/2011

1                     3        06/23/2011    02/15/1997

1                     4        11/12/2011

2                     1        01/13/2011    09/21/1995

2                     2        09/17/2011                        0

3                     1        02/03/2011

3                     2        04/15/2011    11/19/2001

3                     3        07/06/2011    11/19/2001  1          

4                     1        01/29/2011   

4                     2        05/30/2011

4                     3        08/22/2011    07/16/2003  0

4                     4        12/01/2011

I want to (a) make sure that I have the birthdate repeated for the corresponding PATIENT_ID, then I want to (b) create an AGE_AT_DIAG variable for when they were assessed. That is VISIT_DATE - DOB when DIAGNOSIS = 1 or DIAGNOSIS = 0, and then repeated for the corresponding ID. How can I go about this?

[Reeza · Accepted solution]

1. Fill in birth date for all missing records

proc sql;
create table temp1 as
select *, max(dob) as DOB_ALL
from have
group by patient_Id
order by 1, 2, 3;
quit;

2. Calculate age at diagnosis, adding on to step 1 (replaces step1)

proc sql;
create table temp1 as
select *, max(dob) as DOB_ALL,
          yrdif(dob_all, visit_date, 'AGE') as AGE_DIAGNOSIS
from have
group by patient_Id
order by 1, 2, 3;
quit;

3. Merge main table with filtered table for diagnosis = 1

data want;
merge have temp1 (where=(diagnosis=1) keep = (patient_ID DOB_ALL AGE_DIAGNOSIS));
by patient_ID;
run;

---

@amarikow57 wrote:

My dataset has multiple visit dates for a given patient (in long format). Let's say:

PATIENT_ID  VIST  VISIT_DATE  DOB             DIAGNOSIS

1                     1        01/12/2011    02/15/1997  1

1                     2        02/17/2011

1                     3        06/23/2011    02/15/1997

1                     4        11/12/2011

2                     1        01/13/2011    09/21/1995

2                     2        09/17/2011                        0

3                     1        02/03/2011

3                     2        04/15/2011    11/19/2001

3                     3        07/06/2011    11/19/2001  1          

4                     1        01/29/2011   

4                     2        05/30/2011

4                     3        08/22/2011    07/16/2003  0

4                     4        12/01/2011

I want to (a) make sure that I have the birthdate repeated for the corresponding PATIENT_ID, then I want to (b) create an AGE_AT_DIAG variable for when they were assessed. That is VISIT_DATE - DOB when DIAGNOSIS = 1 or DIAGNOSIS = 0, and then repeated for the corresponding ID. How can I go about this?

---