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?
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?
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?
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.