String replacement using reference values

[ABohyn]

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  ?

For example with a table like this one:

data reference;
    input var1 $ var2 $ var3 $;
    datalines;
	2 4.5 17
	;
run;

[FreelanceReinh · Accepted solution]

Hello @ABohyn,

I think a reference table with numeric variables like this

data reference;
input r1-r3;
datalines;
2 4.5 17
;

would be more convenient:

data want(rename=(nvar1-nvar3=var1-var3));
set have;
if _n_=1 then set reference;
array var[3];
array nvar[3];
array r[3];
do i=1 to dim(var);
  nvar[i]=coalesce(input(var[i], ?? 32.),r[i]);
end;
drop i r1-r3 var1-var3;
run;

This would perform the character-to-numeric conversion and replace all non-numeric values of var1-var3 with the corresponding replacement values.

[Patrick]

Here one way that should work

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
;

proc format;
  invalue var1f
    '*' = 2
    other=[best32.]
    ;
  invalue var2f
    '*' = 4.5
    other=[best32.]
    ;
  invalue var3f
    '*' = 17
    other=[best32.]
    ;
run;

data want(drop=_:);
  set have(rename=(var1=_var1 var2=_var2 var3=_var3));
  var1=input(_var1,?? var1f.);
  var2=input(_var2,?? var2f.);
  var3=input(_var3,?? var3f.);
run;

proc print data=want;
run;