topic Re: Calculating time between dates with conditions of per patient ID and by specific observations in SAS Data Management

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

mphqueens — Tue, 14 Jun 2016 19:52:27 GMT

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 🙂

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

ballardw — Tue, 14 Jun 2016 20:11:33 GMT

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.

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

Ksharp — Wed, 15 Jun 2016 06:13:25 GMT

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;