Help: creating a new variable by comparing dates with multi-observations per person

Reply
New Contributor
Posts: 3

Help: creating a new variable by comparing dates with multi-observations per person

[ Edited ]

I need to create a new binary variable, case  (0=no, 1=yes) if a person's disease event date (dz_date) is within a +/- 3 month window of any blood test date (test_date). Each person has many blood test dates but generally only 1 disease date. Each event (disease event or test event)  is listed seperately in my dataset and individuals are linked by an ID number (PID). 

 

My data looks like this:

Obs PID test_date dz_date
1 101 4/1/2013  
2 101 8/22/2013  
3 101 11/10/2013  
4 101   6/18/2013
5 102 2/11/2013  
6 102 8/14/2013  
7 102 12/23/2013  
8 102   2/4/2014
9 203 6/21/2013  
10 203   2/5/2013

 

 

There are over a million observations in this dataset and the # of entries for each PID is varied. One idea I had is to create an upper and lower limit to measure the dz_date against using the below code but I'm not sure if I can compare values working down the PID column. I thought about using first.PID/last.PID...?

 

test_date_lower3= intnx('month',test_date,-3);
test_date_upper3=intnx('month',test_date,+3);

Help!

Valued Guide
Posts: 860

Re: Help: creating a new variable by comparing dates with multi-observations per person

I took a guess at what output you wanted but here is a solution. 

 

data have;
infile cards dsd;
informat pid $3. test_date dz_date mmddyy10.;
format pid $3. test_date dz_date mmddyy10.;
input PID     test_date     dz_date;
cards;
101,4/1/2013,
101,8/22/2013,
101,11/10/2013,
101,,6/18/2013
102,2/11/2013,
102,8/14/2013,
102,12/23/2013,
102,,2/4/2014
203,6/21/2013,
203,,2/5/2013
;

proc sort data=have;by pid test_date dz_date;

data prep;
set have;
by pid;
retain date;
if not missing(dz_date) then date = dz_date;
test = abs(intck('month',test_date,date));
drop date;
run;

proc sql;
create table want as
select distinct pid,
case
when min_test < 4 then '1'
else '0'
end as BiVar from(
select *,min(test) as min_test
from prep
group by pid);

Super User
Posts: 5,437

Re: Help: creating a new variable by comparing dates with multi-observations per person

First a note on data structure, the data set is not normalized. I don't know the consequences in this case, but usually it makes life easier if data is stored in a consistent way.

 

Assuming that the data is sorted by pid and test date.

Then use data step, set, by, and use retain on test date so you can compare with the dz_date.

Also assuming the flag is needed on the dz_date record alone.

Data never sleeps
Valued Guide
Posts: 860

Re: Help: creating a new variable by comparing dates with multi-observations per person

Also, my solution assumes there is a dz_date.

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