Sas Macro dynamic values and look up from another table

[Emma2021]

Hi. I have a dataset (dataset1) as follow (variable name and age).
Name Age
Mike_1 5.0
Sar_2 6.5
Ste_3 8.0

I want to create a dataset that would take values from age
as the following:” from above dataset and modify my another dataset (dataset2):
Data dataset2_mod;
Set dataset2;
If var_name=1 (if variable name’s value has 1 as in Mike_1) then val=5.0;
If var_name=2 (sar_2 has 2) then val=6.5;
Etc.
Run;


I want to modify dataset2 by sas macro parameter that would have …_1, …_2 first identify those (always would have 9 names from 1 to 9) but important the age variables’ values would change so that those should be a macro parameters. How can I do that? Thank you.

[Tom]

Your explanation is not clear. Your dataset does not have variables VARNAME nor VAL. Show working code for a simple example.  Then show a second example and explain what needs to change.

Please show your data as a simple DATA step and not just a a listing.  That way we know the actual variable names, types and lengths.

data have;
  input name :$32. age ;
cards;
Mike_1 5.0
Sar_2 6.5
Ste_3 8.0
;

If you just want to number the observations in your existing dataset then that is simple.

data want;
  var_name +1 ;
  set have;
run;

If the goal is to combine your existing dataset with some other dataset then just use a data step MERGE or perhaps PROC SQL.  But we need to know the variable names in the other dataset that you want to match to NAME so you can attach the value of AGE and call i VAL.

proc sql;
create table want as 
select a.*,b.age as VAL
from other_dataset a
left join have b
on a.var_name = b.name
;
quit;

Or perhaps you just want to create a numeric informat that will convert those NAME values into those AGE values.

proc format;
invalue age
'Mike_1'= 5.0
'Sar_2'= 6.5
'Ste_3'= 8.0
;
quit;

So that you can then use that in another data step.

data want;
 set other;
  val = input(varname,age.);
run;

If so then convert the dataset into control dataset that you can feed into PROC FORMAT.

[Patrick · Accepted solution]

You don't show us how dataset2 looks like so below based on guessing hoping it will give you sufficient guidance to solve the problem with your real data.

data ds1;
  input var $ age;
  datalines;
Mike_1 5.0
Sar_2 6.5
Ste_3 8.0
;

data ds2;
  input var_name $;
  datalines;
2
3
1
4
;

data ds2_mod(drop=_:);
  if _n_=1 then
    do;
      length age 8;
      dcl hash h1();
      h1.defineKey('var_name');
      h1.defineData('age');
      h1.defineDone();
      do until(_done);
        set ds1(keep=var age rename=(var=_var)) end=_done;
        var_name=scan(_var,-1,'_');
        if h1.check() ne 0 then h1.add();
      end;
    end;

  set ds2;
  if h1.find() ne 0 then call missing(age);

run;

proc print data=ds2_mod;
run;

[Kurt_Bremser]

Looks to me like a simple join on a substring:

on scan(t1.name,2,"_") = t2.var_name

Positively no macro coding needed.

[Emma2021]

Thank you-yes indeed -simple and would work too