Read a date from another record in a data set

Reply
New Contributor
Posts: 2

Read a date from another record in a data set

I need SAS to read an onset date from another record on the same patient if the date is missing.  Below is a segment of the current code. 

 

if Date_of_onset=. then Date_of_Onset=collection_date;
else if Date_of_onset=. and collection_date=. then Date_of_Onset=receive_date;
else if Date_of_onset=. and collection_date=. and receive_date=. then Date_of_onset=result_date;
else Date_of_Onset=Date_of_onset;
format date_of_onset lowdate_season uppdate_season lowdate_mmwk uppdate_mmwk mmddyy10.;

Attached is a de-identified exapmle of the data I am using.  This data would normally contain thousands of records, but I have reduced it down to two records as an example. 

 

Valued Guide
Posts: 860

Re: Read a date from another record in a data set

Posted in reply to rramaekers

What's the question?

Valued Guide
Posts: 860

Re: Read a date from another record in a data set

Posted in reply to rramaekers

I'm not sure what the question is but I have some code here, the only difference is I'm using the missing function rather than '= .'

 

data want;
set have;
if missing(Date_of_onset) then Date_of_Onset=collection_date;
else if missing(Date_of_onset) and missing(collection_date) then Date_of_Onset=receive_date;
else if missing(Date_of_onset) and missing(collection_date) and missing(receive_date) then Date_of_onset=result_date;
run;

New Contributor
Posts: 2

Re: Read a date from another record in a data set

Posted in reply to rramaekers

To clarify the question (hopefully).  Typically we will recieve two lab test results on a patient.  The first lab usually has the onset date of illness.  The second lab is confirmatory and often doesn't have the onset date.  I need SAS to somehow match the onset date from the first lab to the second confirmatory lab.  What it is currently doing on lab with no onset date, is reporting back an alternate date like "collection date". 

Valued Guide
Posts: 860

Re: Read a date from another record in a data set

Posted in reply to rramaekers

There's several different ways to do this.  Check your output and make sure this works for you:

 

data have;
infile cards dsd;
informat lab_number $1.  DATE_OF_ONSET mmddyy10.;
format lab_number $1. DATE_OF_ONSET mmddyy10.;
input LAB_NUMBER DATE_OF_ONSET;
cards;
1,10/20/2015
1,10/20/2015
1,
2,10/25/2015
2,
2,
3,10/01/2015
3,10/01/2015
3,
;

proc sort data=have;by lab_number descending date_of_onset;

data want;
update have(obs=0)
       have;
by lab_number;
output;
run;

Super User
Posts: 5,516

Re: Read a date from another record in a data set

Posted in reply to rramaekers

Your data have to be in order by patient for this to make sense.  Here's one way:

 

data want;

set have;

by patient;

if first.patient then first_onset_date=.;

retain first_onset_date;

if date_of_onset > . then first_onset_date = date_of_onset;

else date_of_onset = first_onset_date;

drop first_onset_date;

run;

 

Good luck.

Ask a Question
Discussion stats
  • 5 replies
  • 375 views
  • 0 likes
  • 3 in conversation