Identify all Drugs Taken by A Patient in Order

[cmccor]

Hi,

I have a dataset of patients with a list of drugs they were dispensed in the order they were dispensed. The data looks like this:

Patient	fill_drug1	fill_drug2	fill_drug3	fill_drug4	fill_drug5	fill_drug6	fill_drug7
1	A	A	A	B	C	C	A
2	A	A	A	A	A
3	B	B	B	B	C	A
4	C	C	C	C	C	C
5	C	C	A
6	B	B	B	B	B	B	B
7	A
8	C	A	B	B	A
9	C	C	C	C
10	B	B	B	B	B	B	A

So this data is saying that patient 1 was dispensed drug A, then another dispensing of Drug A, Drug A again, and then Drug B, followed by Drug C twice, and back to Drug A. So there is an order to the sequence of drugs they are dispensed. Patient 2 only got dispensings of Drug A. Patient 8 got Drug C, then Drug A, then Drug B twice, and then back to Drug A.

What I want is something that shows each patient and the drugs they took and in what order. It should look like this:

Patient	Drug1	Drug2	Drug3
1	A	B	C
2	A
3	B	C	A
4	C
5	C	A
6	B
7	A
8	C	A	B
9	C
10	B	A

So this would show that patient 1 got Drug A, B, and C; Patient 2 only got Drug A; patient 8 got Drug C, A, and B. Note that the order matters but only I care about the order in regards to when they first got a specific medication. So the pattern for Patient 8 for example is C, A, B. I don't need to list Drug A again, even though they went back on it, because they originally got it second.

I have been stumpped on how to do this as I can't get the order to work and for the drugs to only show once in the dataset I want. Any advice is appreciated!

[Reeza · Accepted solution]

How many rows of data do you have?

Quickest way is to flip the data (PROC TRANSPOSE), use BY group (+NOTSORTED) to get the first of each drug and re-transpose) but if it's a large data set that may be too big.

---

@cmccor wrote:

Hi,

I have a dataset of patients with a list of drugs they were dispensed in the order they were dispensed. The data looks like this:

Patient	fill_drug1	fill_drug2	fill_drug3	fill_drug4	fill_drug5	fill_drug6	fill_drug7
1	A	A	A	B	C	C	A
2	A	A	A	A	A
3	B	B	B	B	C	A
4	C	C	C	C	C	C
5	C	C	A
6	B	B	B	B	B	B	B
7	A
8	C	A	B	B	A
9	C	C	C	C
10	B	B	B	B	B	B	A

So this data is saying that patient 1 was dispensed drug A, then another dispensing of Drug A, Drug A again, and then Drug B, followed by Drug C twice, and back to Drug A. So there is an order to the sequence of drugs they are dispensed. Patient 2 only got dispensings of Drug A. Patient 8 got Drug C, then Drug A, then Drug B twice, and then back to Drug A.

What I want is something that shows each patient and the drugs they took and in what order. It should look like this:

Patient	Drug1	Drug2	Drug3
1	A	B	C
2	A
3	B	C	A
4	C
5	C	A
6	B
7	A
8	C	A	B
9	C
10	B	A

So this would show that patient 1 got Drug A, B, and C; Patient 2 only got Drug A; patient 8 got Drug C, A, and B. Note that the order matters but only I care about the order in regards to when they first got a specific medication. So the pattern for Patient 8 for example is C, A, B. I don't need to list Drug A again, even though they went back on it, because they originally got it second.

I have been stumpped on how to do this as I can't get the order to work and for the drugs to only show once in the dataset I want. Any advice is appreciated!

---

[cmccor]

It's only about 60,000 patients, so it wouldn't be that long to do. I clearly was overthinking this because I didn't even think of this idea. 

Thank you!