Creating categorical variables using multiple columns

[advmsj]

Hi There,

I am trying to create a categorical variable for drug type that consists of the following categories: 

* drug type 1 only

* drug type 2 only

* drug type 3 only

* multiple drugs

* none

I tried using the follow SAS code but am experiencing difficulties:

data SAS_Test;
input drug1 drug2 drug3;
datalines;
1	1	1
1	0	1
0	0	0
1	0	0
1	0	1
1	1	0
0	0	1
0	1	1
1	1	0
1	0	0
0	1	0
0	0	1
;
run;

data temp_recode1;
set SAS_Test;
if drug1 = 1 then drugtypes = 1;
else if drug2 = 1 then drugtypes = 2;
else if drug3 = 1 then drugtypes = 3;
else if drug1 = 1 and drug2 = 1 then drugtypes = 4;
else if drug1 = 1 and drug3 = 1 then drugtypes = 4;
else if drug2 = 1 and drug3 = 1 then drugtypes = 4;
else drugtypes = 5;
run;

proc freq data=temp_recode1;
tables drug1 drug2 drug3 drugtypes;
run;

thank you!

[Rick_SAS · Accepted solution]

Try this:

data temp_recode1;
set SAS_Test;
sum = sum(drug1, drug2, drug3);
if sum = 0 then drugtypes = 0;
else if sum > 1 then drugtypes = 4;
else if drug1 = 1 then drugtypes = 1;
else if drug2 = 1 then drugtypes = 2;
else if drug3 = 1 then drugtypes = 3;
else drugtypes = .;
drop sum;
run;

proc freq data=temp_recode1;
tables drug1*drugtypes drug2*drugtypes drug3*drugtypes / nocol norow nopercent;
run;

[Angel_Larrion]

Maybe you are having difficulties because there are some cases that overlap like:

if drug1 = 1

and 

else if drug1 = 1 and drug2 = 1

To avoid this you can use the following code:

data temp_recode1;
set SAS_Test;
if sum(drug1,drug2,drug3)=1 then do;
	if drug1 = 1 then drugtypes = 1;
	else if drug2 = 1 then drugtypes = 2;
	else if drug3 = 1 then drugtypes = 3;
 end;

 else if sum(drug1,drug2,drug3)=2 then drugtypes=4;

 else if sum(drug1,drug2,drug3)=3 then drugtypes=5;

 else drugtypes=0; /*when all the drug variables are 0*/

 run;

If you have no drugs, drugtypes will be 5, its this a desirable outcome? if so change the value in the else statement.

[ballardw]

And

if sum(drug1,drug2,drug3)=1 then do;
	if drug1 = 1 then drugtypes = 1;
	else if drug2 = 1 then drugtypes = 2;
	else if drug3 = 1 then drugtypes = 3;
 end;

could be replaced with

if sum(drug1,drug2,drug3)=1 then drugtypes= whichn(1,drug1,drug2,drug3);

The Whichn, and the companion Whichc for character variables, returns  the order number of the first variable/value that is equal to the first value parameter. Consider if you had 10 drug variables, that would be a fair number of IF/Then/Else but the difference with whichn would be to just add the variables to list.

Plus if the variables are in an Array you can use Whichn(<value>, of arrayname(*)) which makes this even shorter for long lists of variables.