Calculating time between dates with conditions of per patient ID and by specific observations

Solved
Occasional Contributor
Posts: 15

Calculating time between dates with conditions of per patient ID and by specific observations

I am not sure if this is possible but I would like to calculate the time between test dates (TESTDATE) for each unique patient ID (ID) with the first test date being where antibody (AB)=1 (for the first time) and the last test date being where rna  (RNA)=1 (for the first time).

data test;

informat ID \$3. TESTDATE yymmdd10. AB \$1. RNA \$1.;

input ID TESTDATE AB RNA;

datalines;

1 2002-07-25 0 0

1 2003-06-20 1 0

1 2003-08-17 1 0

1 2005-01-01 . 1

2 2003-04-06 0 0

2 2006-02-14 1 0

2 2007-01-13 1 1

2 2009-01-04 1 1

3 2008-05-18 0 0

3 2009-02-13 1 0

3 2010-03-10 . 1

;

run;

This is just a sample of my data to get an idea of what I mean. I hope someone can help me! Thank you in advance

Accepted Solutions
Solution
‎08-25-2016 05:53 PM
Super User
Posts: 10,699

Re: Calculating time between dates with conditions of per patient ID and by specific observations

You didn't post output yet.

data test;
informat ID \$3. TESTDATE yymmdd10. AB \$1. RNA \$1.;
input ID TESTDATE AB RNA;
datalines;
1 2002-07-25 0 0
1 2003-06-20 1 0
1 2003-08-17 1 0
1 2005-01-01 . 1
2 2003-04-06 0 0
2 2006-02-14 1 0
2 2007-01-13 1 1
2 2009-01-04 1 1
3 2008-05-18 0 0
3 2009-02-13 1 0
3 2010-03-10 . 1
;
run;

data want;
do until(last.id);
set test;
by id;
TESTDATE_AB=TESTDATE;
found_AB=1;
end;
TESTDATE_RNA=TESTDATE;
found_RNA=1;
end;
end;
dif=TESTDATE_RNA-TESTDATE_AB;

drop found_: TESTDATE_:;
run;

All Replies
Super User
Posts: 13,358

Re: Calculating time between dates with conditions of per patient ID and by specific observations

See if this gets you started:

data want;
set test;
by id;
if first.id then do;
abflag=0;
rnaflag=0;
abdate =.;
end;
if abflag=0 and ab='1' then do;
abdate=testdate;
abflag=1;
end;
if rnaflag=0 and rna='1' then do;
rnaflag=1;
end;
drop abflag rnaflag;
run;

You could add an output statement after the betweendates assignment to just output one record per id.

Solution
‎08-25-2016 05:53 PM
Super User
Posts: 10,699

Re: Calculating time between dates with conditions of per patient ID and by specific observations

You didn't post output yet.

data test;
informat ID \$3. TESTDATE yymmdd10. AB \$1. RNA \$1.;
input ID TESTDATE AB RNA;
datalines;
1 2002-07-25 0 0
1 2003-06-20 1 0
1 2003-08-17 1 0
1 2005-01-01 . 1
2 2003-04-06 0 0
2 2006-02-14 1 0
2 2007-01-13 1 1
2 2009-01-04 1 1
3 2008-05-18 0 0
3 2009-02-13 1 0
3 2010-03-10 . 1
;
run;

data want;
do until(last.id);
set test;
by id;
TESTDATE_AB=TESTDATE;
found_AB=1;
end;
TESTDATE_RNA=TESTDATE;
found_RNA=1;
end;
end;
dif=TESTDATE_RNA-TESTDATE_AB;

drop found_: TESTDATE_:;
run;
🔒 This topic is solved and locked.