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

[mphqueens]

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 🙂 

[ballardw]

See if this gets you started:

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

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

[Ksharp · Accepted solution]

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;
  if AB=1 and not found_AB then do;
   TESTDATE_AB=TESTDATE;
   found_AB=1;
  end; 
  if RNA=1 and not found_RNA then do;
   TESTDATE_RNA=TESTDATE;
   found_RNA=1;
  end; 
end;   
  dif=TESTDATE_RNA-TESTDATE_AB;

 drop found_: TESTDATE_:;
run;