BookmarkSubscribeRSS Feed
JillChen0131
Fluorite | Level 6

I have some patients' medication data, need to convert it to binary table. Anyone has the solution?

 

what I have:

PatientMedication1Medication2Medication3
ID1ABC
ID2CEF
ID3EGH

 

what I want:

PatientABCDEFGH
ID1111     
ID2  1 11  
ID3    1 11
2 REPLIES 2
PaigeMiller
Diamond | Level 26

I am skeptical about this re-arrangement of the data. In the real world, medications are going to have names like Hydroxyzine, and then do you really want SAS variables to be named Hydroxyzine and AcetylSalicilic and so on? In addition, I can't see how this proposed re-arrangement of the data is useful for additional analyses.

 

A much better re-arrangement would be

 

data have;
input Patient $ Medication1	$ Medication2 $	Medication3 $;
cards;
ID1	A	B C
ID2	C	E F
ID3	E	G H
;

data want;
    set have;
    medication=medication1;
    output;
    medication=medication2;
    output;
    medication=medication3;
    output;
    drop medication1-medication3;
run;

 

 

--
Paige Miller
ballardw
Super User

First a comment on "binary". That means two values, typically 1 and 0. You only show one value, the 1, which would technically be unary.

 

For your given example this creates binary values, 1 where present and 0 where not. Caveats to follow:

data have;
input Patient $	Medication1 $	Medication2 $	Medication3 $;
datalines;
ID1	A	B	C
ID2	C	E	F
ID3	E	G	H
;

data want;
   set have;
   array m (*) medication: ;
   array n (*) A B C D E F G H;
   array v (8) $ 1 _temporary_('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H');
   do i=1 to dim(v);
      n[i] = whichc(v[i],of m(*))>0;
   end;
   drop Medication: i;
run;

 

The WHICHC function searches for matches of the first parameter in the remaining list of values and returns the position number if found or 0 if not found. This match is case sensitive. So with this ALL of your values may need to have the case standardized.

I have assumed that your actual values are likely to be different than actually the letters A B etc. due to the name for the variables with Medication. I suspect you actually have medication names. If so you need to change the names of the variables in the N array to something acceptable as SAS variable names, no hyphens, spaces or slash characters. The values in the V array would be the standardized spelling of the actual words that appear and the number following the $ needs to be large enough to hold the longest actual text that appears.

 

Arrays are one way to handle multiple variables in a similar way. Also a group of values may often be addressed with the "of arrayname(*)" syntax in functions that allow lists of values.

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 388 views
  • 0 likes
  • 3 in conversation