topic Identify all Drugs Taken by A Patient in Order in SAS Programming

Identify all Drugs Taken by A Patient in Order

cmccor — Wed, 11 Jan 2023 22:02:55 GMT

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!

Re: Identify all Drugs Taken by A Patient in Order

Reeza — Wed, 11 Jan 2023 22:33:09 GMT

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!

Re: Identify all Drugs Taken by A Patient in Order

cmccor — Wed, 11 Jan 2023 22:42:35 GMT

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!