This looks like an unusually complex approach. I enjoy the macro language, and CALL EXECUTE, but I think it's rare to use CALL EXECUTE to execute the code to define a macro. In this case, when the DATA step runs it will generate code to create the macro LOOKUPTBL. The macro itself has no parameters but in a sense it's dynamic because you're generating the macro source code dynamically. If there are 10 records in work.inforcelisting5 you'll create the macro LOOKUPTBL 10 times, and each time it could have a different definition.
I would think this would be easier to design, debug, and maintain if you just define a macro with a few parameters:
%macro lookuptbl (data=,varname1=, varname2=, varname3=);
...
%mend;
With that approach you could still use CALL EXECUTE to generate the macro calls, and you could use if VAREXIST to decide which variable names to pass into each macro call.
Or if you want to leave the variable exists tests in the macro, you could do it like:
%macro lookuptbl(data);
%local varname1 varname2 varname3;
%if %varexist(&data,police) %then %let varname1=police;
%else %if %varexist(&data,agreement_nbr) %then %let varname1=agreement_nbr;
*...;
%put &=varname1 &=varname2 &=varname3;
proc sql;
create table table_info as
select distinct
strip(a.&varname1.) as agreement_nbr length=20
,strip(cie) as cie length=2',
/* ... */
;
quit;
%mend lookuptbl;
The idea of dynamically generating macro definitions is kind of scary to me, but I'm sure people do it. Usually macros are dynamic enough, especially when paired with using CALL EXECUTE or similar to generate macro calls.
... View more