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

Hi, I am using SAS 9.4. I have wide data of all the drugs used by patients that looks like this:

 

ID Drug1 Drug2 Drug3 Drug4 Drug5 Drug6 Drug7
1 Ceftazidme Ciprofloxacin          
2 Ceftazidme Colistin Vancomycin      
3 Cefazolin Ceftazidme Meropenem      
4 Cefepime Minocycline Colistin        
             

 

I would like to be able to tell how many people used each type of drug no matter which column the drugs are in, which is hard to do with the way my data are now (e.g., I want to know that 3 people used Ceftazidme, 2 people used Colistin, etc).

I would like my data to look like this:

 

ID Ceftazidme Ciprofloxacin Colistin Vancomycin Cefazolin Meropenem Cefepime Minocycline Drug45
1 1 1 0 0 0 0 0 0    
2 1 0 1 0 0 0 0 0    
3 1 0 0 0 1 1 0 0    
4 0 0 1 0 0 0 1 1    
                   

Although each ID had a maximum of 7 drugs, there are 45 unique drug names among all the IDs, so it's a little unwieldy to write out arrays for each drug name.

Any ideas would be great! Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

Try this

 

data have;
input ID (Drug1 - Drug7)(:$20.);
infile datalines missover;
datalines;
1 Ceftazidme Ciprofloxacin        
2 Ceftazidme Colistin Vancomycin  
3 Cefazolin Ceftazidme Meropenem  
4 Cefepime Minocycline Colistin   
; 

data temp(keep = ID v drug);
   set have;
   array d Drug1 - Drug7;
   do over d;
      drug = d;
      v    = 1;
      if drug ne '' then output;
   end;
run;

proc transpose data = temp out = want(drop = _:);
   by ID;
   id drug;
   var v;
run;

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

Try this

 

data have;
input ID (Drug1 - Drug7)(:$20.);
infile datalines missover;
datalines;
1 Ceftazidme Ciprofloxacin        
2 Ceftazidme Colistin Vancomycin  
3 Cefazolin Ceftazidme Meropenem  
4 Cefepime Minocycline Colistin   
; 

data temp(keep = ID v drug);
   set have;
   array d Drug1 - Drug7;
   do over d;
      drug = d;
      v    = 1;
      if drug ne '' then output;
   end;
run;

proc transpose data = temp out = want(drop = _:);
   by ID;
   id drug;
   var v;
run;
Lefty
Obsidian | Level 7
Amazing! Works perfectly. Thank you!!
HB
Barite | Level 11 HB
Barite | Level 11

I would argue you actually don't want your data in your binary column arrangement.

Add a new drug and you have to add a new column.   

 

I would argue more normalized data like:

 

ID Drug Date or dosage or whatever
1 Ceftazidme  
1 Ciprofloxacin  
2 Ceftazidme  
2 Colistin  
2 Vancomycin  
3 Cefazolin  
3 Ceftazidme  
3 Meropenem  
4 Minocycline  
4 Colistin  

 

which is essentially the format of the TEMP table in the @PeterClemmensen code would you do you more favors in the long run in terms of querying. 

 

proc sql;
	select drug, count(id) as "Number of IDs Taking Drug"n
	from temp
	group by drug;
quit;
drug Number of IDs Taking Drug
Cefazolin 1
Cefepime 1
Ceftazidme 3
Ciprofloxacin 1
Colistin 2
Meropenem 1
Minocycline 1
Vancomycin 1

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
  • 3 replies
  • 380 views
  • 1 like
  • 3 in conversation