Create new variable based on conditions across multiple variables in SAS

2
votes

I would like to create a new variable "type" based on conditions being true across multiple variables, but I have too many variables (~100) to type. I am using SAS Studio v 9.4.

My data is set up similar to this:

DATA have;
    INPUT id  
    a_var_a a_var_b a_var_c a_var_d a_var_e
    b_var_a b_var_b b_var_c b_var_d
    c_var_a c_var_b c_var_c d_var_d;
    DATALINES;
          01 1 0 0 0 0 0 0 0 0 0 0 0 0
          02 0 1 0 0 0 0 0 0 0 0 0 0 0
          03 0 0 1 0 0 0 0 0 0 0 0 0 0
          04 0 0 0 1 0 0 0 0 0 0 0 0 0
          05 0 0 0 0 1 0 0 0 0 0 0 0 0
          06 0 0 0 0 0 1 0 0 0 0 0 0 0
          07 0 0 0 0 0 0 1 0 0 0 0 0 0
          08 0 0 0 0 0 0 0 1 0 0 0 0 0
          09 0 0 0 0 0 0 0 0 1 0 0 0 0
          10 0 0 0 0 0 0 0 0 0 1 0 0 0
          11 0 0 0 0 0 0 0 0 0 0 1 0 0
          12 0 0 0 0 0 0 0 0 0 0 0 1 0
          13 0 0 0 0 0 0 0 0 0 0 0 0 1  
          ;
Run;

"type" is coded as:

  • 1 If any of the group a vars (a_var:) are equal to 1
  • 2 If any of the group b vars (b_var:) are equal to 1
  • 3 If any of the group c vars (c_var:) are equal to 1
  • else equal to 0

I thought it would be as simple as:

Data want;
   Set have;

   If a_var: = 1 then type = 1;
   Else If b_var: = 1 then type = 2;
   Else If c_var: = 1 then type = 3;
   Else type = 0;
Run;

However I keep getting an error code because I am not allowed to group the variables.

I tried doing the same thing with an array but I am still unable to arrive at a solution:

Data want;
  Set have;

  Array a (*) a_var:;
  Array other (2,4) b_var: c_var:;

  do i = 1 to dim(a);
  If a(i) = 1 then type=1;
  end;

  do i = 1 to 4;
  If other (1,i) = 1 then type=2;
  If other (2,i) = 1 then type=3;
  Else type=0;
  end;

  drop i;
Run;

I am trying to create 3 categories of the "type" variable (0,1,2, and 3) based on how the conditions are met.

2
sas

2 Answers

1
votes

Thank you!

This is the code eventually worked.

DATA have;
  INPUT id

    a_var_a a_var_b a_var_c a_var_d a_var_e
    b_var_a b_var_b b_var_c b_var_d
    c_var_a c_var_b c_var_c c_var_d;

    if whichn (1, of a_var: ) =>1 then type=1;
    else if whichn (1, of b_var: ) =>1 then type=2;
    else if whichn(1, of c_var:) =>1 then type=3;
    else type = 0;
DATALINES;
01 1 0 0 0 0 0 0 0 0 0 0 0 0
02 0 1 0 0 0 0 0 0 0 0 0 0 0
03 0 0 1 0 0 0 0 0 0 0 0 0 0
04 0 0 0 1 0 0 0 0 0 0 0 0 0
05 0 0 0 0 1 0 0 0 0 0 0 0 0
06 0 0 0 0 0 1 0 0 0 0 0 0 0
07 0 0 0 0 0 0 1 0 0 0 0 0 0
08 0 0 0 0 0 0 0 1 0 0 0 0 0
09 0 0 0 0 0 0 0 0 1 0 0 0 0
10 0 0 0 0 0 0 0 0 0 1 0 0 0
11 0 0 0 0 0 0 0 0 0 0 1 0 0
12 0 0 0 0 0 0 0 0 0 0 0 1 0
13 0 0 0 0 0 0 0 0 0 0 0 0 1
14 0 0 0 0 0 0 0 0 0 0 0 0 0
;
Run;
0
votes

I don't think the prefix: shortcut can be used for something like this.

Instead I suggest you use macros to generate the code you need based on DICTIONARY.COLUMNS (see data set column names into macro variable(s) for an example).

You can generate conditions like a_var_a=1 or a_var_b=1 or a_var_c=1 or a_var_d=1 or a_var_e=1 using something like this (untested):

/* preferably enclose this in a macro and declare the macrovariable as %local mvGroupAIsSet; */
proc sql noprint;
    select cats(name, '=1') into :mvGroupAIsSet separated by ' or '
    from dictionary.columns
    where name like 'a_var_%' /* don't remember if you need to escape the underscores */
      and libname = 'WORK'
      and memname = 'HAVE';
quit;

Then use this in your DATA step:

data want;
   set have;

   if &mvGroupAIsSet then type = 1;
   /* etc */
run;