BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mphqueens
Fluorite | Level 6

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 🙂 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

2 REPLIES 2
ballardw
Super User

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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