SAS: string replacement using reference value

Question

I have a dataset with one id column and three character variables:

data have;
    input id var1 $ var2 $ var3 $;
    datalines;
    1 * 1.2 *
    2 * 3.7 0.3
    3 0.7 * 2
    4 4.9 4.0 1.8
    ;
run;

For each of the character variable, I want to replace the '*' character with a string that is specific to the variable. For example, in var1 '*' would be replace by '2', then in var2 '*' would be replaced by '4.5', etc... And then I would like to turn these variables to numeric. Is there a way of doing this using a "reference" table with the replacement values ?

Answer 1

You can use an array to store the replacement values for the * inputs. You will need to use the INPUT() function to convert the character value to a numeric value. Note: SAS DATA Step is not like scripting languages that allow variables to change 'types' on the fly at run-time.

Example:

Each arrayed character variable is converted to a numeric value saved in a separate variable. rename= is used in the output data set options to change the name of the numeric variables back to the original corresponding names.

data want(rename=(num1-num3=var1-var3)) ;
  set have ;
  array char var1-var3 ;
  array num  num1-num3 ; 
  array r [3] _temporary_ (2, 4.5, 13) ;
  do over char ;
    if char = '*' then num = r[_i_]; else num = input(char, best.);
  end ;
  drop var1-var3;
run ;