SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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 Smiley Happy 

 


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

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

Posted in reply to mphqueens

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;

View solution in original post


All Replies
Super User
Posts: 11,343

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

Posted in reply to mphqueens

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.

 

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

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

Posted in reply to mphqueens

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 214 views
  • 0 likes
  • 3 in conversation