Hello @cchubbard1963,
@cchubbard1963 wrote:
For example, a patient has tests on Jan. 1, 2019, Jan 15, 2019, June 1, 2019 and July 1, 2019. Based upon these criteria, this patient is considered at risk starting on Jan. 15, 2019 (second test within 24 weeks), and remains at risk until Apr 9, 2019 (12 weeks after 1/15/19), at which time they are no longer at risk, until July 1, 2019 (second test within 24 weeks), at which time they are again at risk.
I take it that criterion (1) is not applied to the two tests on Jan 15, 2019 and June 1, 2019 (which are less than 24 weeks apart) because after the 12-week gap following Jan 15, 2019 the test on June 1, 2019 is regarded as the first test of a new series.
One way to tackle this type of problem is what Paul Dorfman calls the "paintbrush approach." For each patient the days at risk are marked in a sufficiently large array (like a "calendar"). In my suggestion below I set start and end dates covering the test dates of all patients in your HAVE dataset plus 12 weeks follow-up. (You could also make this dynamic, i.e., derive start and end from dataset HAVE.) I'm not sure if "the study period" you refer to is common for all patients or if each patient has an individual study period. In the latter case you would need to restrict the WANT dataset to those individual study periods. This is done in the "optional" part of the code shown below (your definition of the individual study periods may be different, though).
/* Define a common study period for all patients (incl. follow-up) */
%let start = %sysevalf('01JAN2018'd);
%let end = %sysevalf('31DEC2020'd);
/* Create at-risk flag ATRISK in (0, 1) on patient-date level */
data _tmp(keep=id date atrisk) / view=_tmp;
array a[&start:&end] _temporary_;
do t=1 by 0 until(last.id);
set have;
by id date;
/* if last.date then do; */ /* use this to treat multiple tests on the same date as one test */
if .<r<date then do; /* note the final incrementation of r in the iterative DO loop below */
t=1; /* reset "test counter" t after a 12-week gap */
r=.;
end;
if t>=2 & intck('week',prevdate,date,'c')<24 then
do r=coalesce(r,date) to intnx('week',date,12,'s')-1;
a[r]=1;
end;
prevdate=date;
t+1;
/* end; */ /* use this to treat multiple tests on the same date as one test */
end;
do date=&start to &end;
atrisk=(a[date]=1);
output;
end;
call missing(of a[*]);
run;
/* Aggregate at-risk flags on patient-month level */
proc summary data=_tmp;
by id date;
format date monyy7.;
var atrisk;
output out=want(drop=_:) max=;
run;
/* Optional: Restrict WANT dataset to individual study periods */
proc sql;
create table periods as
select id, intnx('month',min(date),0,'b') as _start,
intnx('month',intnx('week',max(date),12,'s')-1,0,'e') as _end
from have
group by id
order by id;
quit;
data final(drop=_:);
merge want periods;
by id;
if _start<=date<=_end;
run;
For validation purposes you may want to create a physical dataset instead of the view _TMP. This dataset might be large, as the number of observations equals the number of patients times the number of days in the overall study period (&end-&start+1), e.g., 48*1096=52608 for your sample data. For example, you may want to check if borderline cases (such as a test exactly 12 or 24 weeks after the previous one) are handled as required. With the above implementation a 12-week gap would mean at least one day with ATRISK=0 before the next test a new phase with ATRISK=1 begins (if any). Duplicate dates for a patient (there is one such case in your sample data: id=6149, date='21APR2019'd) are treated as separate tests. In particular, the patient will be considered "at risk" immediately on that date. If this is not desired (i.e., multiple tests on the same day should be treated as one test), just activate the IF-THEN-DO-END statements that I have commented out.
I haven't used variable VALUE in my code. So if there is a criterion regarding this variable (e.g., value>. for a valid test), this will need to be added. Similarly, I haven't considered missing dates (as these don't occur in your sample data and could be filtered easily).
