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!
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);
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.
Also, my solution assumes there is a dz_date.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.