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

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:

 
 
Patientfill_drug1fill_drug2fill_drug3fill_drug4fill_drug5fill_drug6fill_drug7
1AAABCCA
2AAAAA  
3BBBBCA 
4CCCCCC 
5CCA    
6BBBBBBB
7A      
8CABBA  
9CCCC   
10BBBBBBA

 

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:

PatientDrug1Drug2Drug3
1ABC
2A  
3BCA
4C  
5CA 
6B  
7A  
8CAB
9C  
10BA 

 

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
Reeza
Super User

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!




View solution in original post

2 REPLIES 2
Reeza
Super User

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
Fluorite | Level 6

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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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