topic Help: creating a new variable by comparing dates with multi-observations per person in SAS Programming

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

mh3477 — Tue, 13 Oct 2015 15:05:18 GMT

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!

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

Steelers_In_DC — Tue, 13 Oct 2015 15:59:31 GMT

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);

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

LinusH — Tue, 13 Oct 2015 16:03:44 GMT

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.

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

Steelers_In_DC — Tue, 13 Oct 2015 16:18:07 GMT

Also, my solution assumes there is a dz_date.