Categorising current treatment by looking ahead

[asterina]

Hi all,

I wonder if you can please help me on something that has got me stuck over the whole of last week!

I have visit information for a large number of patients. Every patient has a varying number of visits and visit date. The task is to classify the current medication treatment they have been prescribed by their doctor by looking ahead over the next 6 months and considering what else they may have got prescribed in the future (of next 180 days). For simplicity of describing this example, think of it whether we classify this as a concurrent medication or not - depending if they have prescribed anything else in the future also.

So I am really stuck how to do this - there is a large number of patients, a large number of records for each - some patients visited nearly every day, others once every 8 months. The information goes up to a few years for each. So it's tricky to set any hardcoded rules for them, or indeed to just look at next few observations.

Here is an example of what the data looks like. So for this example, we want to know if Patient1 was prescribed anything else over next 180 days other than drug x, which would then allow us to create a new variable indicating that "drug x" was given as a concurrent medication. ny example codes on how to solve this would be massively appreciated.

	Visit_date	Max_date	Trt_at_visit
Patient1	01/12/00	30/05/01	Drug_x
Patient1	31/12/00	29/06/01	Drug_x
Patient1	24/03/01	20/09/01	Drug_e
Patient1	22/07/01	18/01/02	Drug_x
Patient1	02/12/00	31/05/01	Drug_x
Patient1	01/01/01	30/06/01	Drug_x
Patient1	25/03/01	21/09/01	Drug_x
Patient1	23/07/01	19/01/02	Drug_x
Patient1	20/11/01	19/05/02	Drug_a
Patient1	20/12/01	18/06/02	Drug_x
Patient1	13/03/02	09/09/02	Drug_x
Patient1	11/07/02	07/01/03	Drug_x
Patient1	08/11/02	07/05/03	Drug_x
Patient1	08/12/02	06/06/03	Drug_x
Patient1	01/03/03	28/08/03	Drug_x
Patient2	29/06/03	26/12/03	Drug_x
Patient2	27/10/03	24/04/04	Drug_e
Patient2	26/11/03	24/05/04	Drug_x
Patient2	17/02/04	15/08/04	Drug_x
Patient2	16/06/04	13/12/04	Drug_x
Patient2	14/10/04	12/04/05	Drug_e
Patient2	13/11/04	12/05/05	Drug_x
Patient2	04/02/05	03/08/05	Drug_x
Patient2	04/06/05	01/12/05	Drug_x
Patient2	02/10/05	31/03/06	Drug_x
Patient2	01/11/05	30/04/06	Drug_x
Patient2	23/01/06	22/07/06	Drug_x
Patient2	23/05/06	19/11/06	Drug_x

[ballardw]

Can you provide an example of the desired output for Patient1 based on the example data?

What role if any does the Max_date variable have?

[asterina]

thanks! Max_date is just the visit date window (visit date +180 days). I would like a new variable which will recode the treatment variable based on its current value - e.g. Single medication or conmed

[DBailey]

proc sql;

create table want as

select

     t1.patient,

     t1.visit_date,

     t1.Trt_at_visit,

     sum(case when t1.trt_at_visit = t2.trt_at_visit then 1 else 0 end) as Future_Rx_Current_Med,

     sum(case when t1.trt_at_visit <> t2.trt_at_visit then 1 else 0 end) as Future_Rx_Different_Med

from

     have t1

     left outer join have t2

          on t1.patient = t2.patient

          and t2.visit_date > t1.visit_date

          and t1.visit_date < t1.visit_date + 180

group by

     t1.patient,

     t1.visit_date,

     t1.trt_at_visit;

quit;

[Haikuo]

Was not aware of you have done it yesterday ago, was still waiting on your new post. anyway, here is my suggested solution:

data have;

     infile cards dlm='09'x truncover;

     input patient:$10.   (Visit_date     Max_date) (:ddmmyy10.)     Trt_at_visit$;

     format visit_date Max_date ddmmyy10.;

     cards;

Patient1   01/12/00   30/05/01   Drug_x

Patient1   31/12/00   29/06/01   Drug_x

Patient1   24/03/01   20/09/01   Drug_e

Patient1   22/07/01   18/01/02   Drug_x

Patient1   02/12/00   31/05/01   Drug_x

Patient1   01/01/01   30/06/01   Drug_x

Patient1   25/03/01   21/09/01   Drug_x

Patient1   23/07/01   19/01/02   Drug_x

Patient1   20/11/01   19/05/02   Drug_a

Patient1   20/12/01   18/06/02   Drug_x

Patient1   13/03/02   09/09/02   Drug_x

Patient1   11/07/02   07/01/03   Drug_x

Patient1   08/11/02   07/05/03   Drug_x

Patient1   08/12/02   06/06/03   Drug_x

Patient1   01/03/03   28/08/03   Drug_x

Patient2   29/06/03   26/12/03   Drug_x

Patient2   27/10/03   24/04/04   Drug_e

Patient2   26/11/03   24/05/04   Drug_x

Patient2   17/02/04   15/08/04   Drug_x

Patient2   16/06/04   13/12/04   Drug_x

Patient2   14/10/04   12/04/05   Drug_e

Patient2   13/11/04   12/05/05   Drug_x

Patient2   04/02/05   03/08/05   Drug_x

Patient2   04/06/05   01/12/05   Drug_x

Patient2   02/10/05   31/03/06   Drug_x

Patient2   01/11/05   30/04/06   Drug_x

Patient2   23/01/06   22/07/06   Drug_x

Patient2   23/05/06   19/11/06   Drug_x

;

proc sql;

     create table want as

           select *, exists(select * from have where   patient=a.patient

                and a.visit_date <= visit_date <= a.visit_date+180

                and Trt_at_visit ne a.Trt_at_visit

                ) as Concurrent_flag

     from have a

     ;

quit;

Good luck,

Haikuo