topic Re: variable categorization in SAS Programming

variable categorization

CathyVI — Thu, 04 May 2023 21:22:46 GMT

Hi,
I am trying to generate the following drug categories. I made an attempt but not sure if I'm right. Any help will be very appreciated.
1. if any of the drugs = 0 then drug = 0
2. if only one drug=1 then drug =1
3. if More than one drug= 2 then drug= 2
Here is my code attempt

data want;
set have;
if acet_drug=0 & tri_drug=0 & BARBS_drug=0 & mari_drug then drug =0;
else if acet_drug=1 | tri_drug=1 | BARBS_drug=1 | mari_drug=1 then drug=1;
else if acet_drug=2 & tri_drug=2 & BARBS_drug=2 & mari_drug=2 then drug=2 ;
run;

Re: variable categorization

Quentin — Thu, 04 May 2023 21:35:29 GMT

Hi,

Your categories are not mutually exclusive. It's possible for a single record to be true for all three categories. It looks like you want to categorize the record into the first true category, correct? i.e. in your code you use if/else if/else if, but that is not stated in the logic description.

I don't think your current code will give you what you want. One good way to check is with PROC FREQ. If you run:

proc freq data=want;
  tables drug*acet_drug*tri_drug*BARBS_drug*mari_drug /missing list;
run;

That will produce a nice table showing how each combination of values in your data was mapped to a drug category.

Just reading your logic, it's confusing. I wonder if you might want:

1. if all the drugs = 0 then drug = 0
2. if only one drug=1 then drug =1
3. if More than one drug=1 then drug= 2

What are the values you have for the drug variables. Are they just coded 0 or 1? Or are they coded 0, 1, 2?

Re: variable categorization

CathyVI — Thu, 04 May 2023 23:47:30 GMT

@Quentin Thank you for your great suggestion. You are right about the data not mutually exclusive. Here is the snapshot of the proc freq you suggested

I think doing this might be better as you've suggested. My data is just 0,1,2

1. if all the drugs = 0 then drug = 0
2. if only one drug=1 then drug =1
3. if More than one drug=1 then drug= 2

What code will be a better code?

Re: variable categorization

Quentin — Thu, 04 May 2023 23:50:16 GMT

Hmm, I was hoping your data was just 0/1 for No/Yes. What do 0, 1, and 2 mean?

Re: variable categorization

Tom — Fri, 05 May 2023 00:27:15 GMT

There seem to be some holes in your rules.

First let's try it with only 2 input variables. First let's make a dataset with all possible combinations of 0,1 and 2.

data have;
  do drug1=0,1,2; do drug2=0,1,2; 
    output;
  end;end;
run;

Now let's apply your rules (in the order you state them).

%let list=drug1 drug2 ;

data want;
  set have;
/* 1. if any of the drugs = 0 then drug = 0 */
  if countc(cats(of &list),'0') then drug=0;
/* 2. if only one drug=1 then drug =1 */
  else if 1=countc(cats(of &list),'1') then drug=1;
/* 3. if More than one drug= 2 then drug= 2 */
  else if 1<countc(cats(of &list),'2') then drug=2;
/* 4. ???? */
  else drug=.;
run;

Result

So none of your rules covers the case when there are no zeros and more than 1 one and less than 2 twos.