topic Re: Categorising current treatment by looking ahead in SAS Procedures

Categorising current treatment by looking ahead

asterina — Tue, 09 Dec 2014 22:25:20 GMT

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

Re: Categorising current treatment by looking ahead

ballardw — Tue, 09 Dec 2014 22:39:52 GMT

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?

Re: Categorising current treatment by looking ahead

asterina — Tue, 09 Dec 2014 22:52:30 GMT

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

Re: Categorising current treatment by looking ahead

DBailey — Wed, 10 Dec 2014 19:59:17 GMT

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;

Re: Categorising current treatment by looking ahead

Haikuo — Wed, 10 Dec 2014 20:28:58 GMT

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