10-13-2015 10:55 AM - edited 10-13-2015 11:05 AM
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:
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);
10-13-2015 11:59 AM
I took a guess at what output you wanted but here is a solution.
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;
proc sort data=have;by pid test_date dz_date;
if not missing(dz_date) then date = dz_date;
test = abs(intck('month',test_date,date));
create table want as
select distinct pid,
when min_test < 4 then '1'
end as BiVar from(
select *,min(test) as min_test
group by pid);
10-13-2015 12:03 PM
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.