☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## Identify all Drugs Taken by A Patient in Order

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Identify all Drugs Taken by A Patient in Order

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!

2 REPLIES 2
Super User

## Re: Identify all Drugs Taken by A Patient in Order

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!

Fluorite | Level 6

## Re: Identify all Drugs Taken by A Patient in Order

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!

Discussion stats
• 2 replies
• 374 views
• 2 likes
• 2 in conversation