topic Re: Creating categorical variables using multiple columns in SAS Programming

Creating categorical variables using multiple columns

advmsj — Tue, 15 Sep 2020 15:14:36 GMT

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!

Re: Creating categorical variables using multiple columns

Rick_SAS — Tue, 15 Sep 2020 15:34:32 GMT

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;

Re: Creating categorical variables using multiple columns

Angel_Larrion — Tue, 15 Sep 2020 15:39:59 GMT

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.

Re: Creating categorical variables using multiple columns

ballardw — Tue, 15 Sep 2020 15:47:17 GMT

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.