How to reference another table in sas macro

Question

I'm trying to use a parametric table as a sas format. I have tried many ways but any of them works. My ideas are.

1. With a proc format. I am not sure if I can use two specific columns from a dataset to build a SAS format.

2. To build a SAS Macro which does something similar than a format.

   %macro test(var1=);
   %GLOBAL &var_aux;
   proc sql noprint;
   Select trasformated_value into :&var_aux
    from parametric_table
    where original_value=&var1.;
    var1=&var_aux;
    quit;
    drop &var_aux;
   %mend;
   

The problem with the macro is that I don't know how to return a value to the original query.

data transformation;
set transformation;
New_value = %test(old_value);
run;

Answer 1

You'd need to write a function style macro if you were to do what you're doing above, which isn't really easy to do (possible, but not sufficiently easy to be worth doing).

Macros aren't functions by nature, they're just code that you write elsewhere and repeat. So you can't really call a macro on the right-hand side of the equal sign unless the only non-macro code it includes is code valid on the right hand side of an equal sign.

In this case, I agree with format as the solution. You need to use the cntlin option in proc format, and you would need to create the format before you run the data step.

You need fmtname, start, label at minimum. type is also commonly included. hlo="o" row is also a good idea (for "other").

data for_fmt;
  set parametric_table;
  retain fmtname 'PARAMETF' type 'N'; *or 'C' and include a $ if character;
  rename 
    original_Value = start
    transformed_value = label
  ;
  output;
  if _n_=1 then do;
    hlo='o';
    call missing(original_value);
    transformed_value = .; *or ' ' or whatever you want a non-match to return;
     output;
  end;
run;

proc format cntlin=for_fmt;
quit;

Make sure that you don't have duplicate start values, but otherwise this is your best approach for what you're describing. Then you have

data transformation;
  set transformation;
  new_value = input(put(old_value,PARAMETF.),BEST12.); *or whatever depending on what you are doing.  Format makes CHAR value always, so `input` to convert to number.;
run;

No need for macros here, though you certainly could (And, I'd say, should) have a generic macro for creating formats like this.