I have a data set consisting of a longitudinal test results on a cohort of patients. The data set consists of the following variables: patient ID ("ID), test results ("VALUE"), and test date ("DATE"). A fake sample data set with this structure is attached. The goal of the research is to determine if there is a change in testing frequency over various time frames. In order to develop a denominator of patients "at risk" in the definition of the testing rate, the researchers have developed the following criteria to determine if a patient should contribute to the denominator: (1) if a patient has two tests within 24 weeks, then they are considered "at risk" beginning on the date of the second test and contribute to the denominator. (2) If an at risk patient has a gap of 12 weeks without a test, then after 12 weeks they are no longer considered at risk and do not contribute to the denominator thereafter, except ... (3) Patients dropped from the at risk population because of criteria No. 2 can reenter the cohort, and be considered at risk at later time periods, if they meet criteria No. 1 in the subsequent time period. So, patients can enter and leave the at risk population. 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 need to create a data set with a row for each patient for each month in the study period, that would look something like this: ID Month At Risk 1256 Jan 2019 1 1256 Feb 2019 1 1256 Mar 2019 1 1256 Apr 2019 1 1256 May 2019 0 (Patients are at risk for the month if they are at risk on any day of that month.) If criteria Nos. 1 and 2 had the same time period (12 weeks or 24 weeks), I think I know how to program this. However, the differing time periods have thwarted my efforts thus far. Any insights or suggestions would be greatly appreciated. Thanks in advance!
... View more