Building Intelligent Macros: Driving a Variable Parameter System with Metadata

[#1]

Abstract and online materials for this paper prepared for WUSS 2017.

Abstract

When faced with generating a series of reports, graphs, and charts; we will often use the macro language to simplify the process. Commonly we will write a series of generalized macros, each with the capability of creating a variety of outputs that depend the macro parameter inputs. For large projects, potentially with hundreds of outputs, controlling the macro calls can itself become difficult.

The use of control files (metadata) to organize and process when a single macro is to be executed multiple times was discussed by Rosenbloom and Carpenter (2015). But those techniques only partially help us when multiple macros each with its own set of parameters are to be called. This paper discusses a technique that allows you to control the order of macro calls along with each macro's parameter set using a metadata control file.

Excerpt from an example in the paper

Online Materials

View the PDF version of the complete paper here.

Sample code and control files are attached as a ZIP file

[SASKiwi]

Great paper. Metadata-driven macros are incredibly powerful and I've used the same techniques for not only doing general reporting but also for automating ETL and format-creation processes. The huge advantage is that all maintenance is done by modifying the metadata and not the macro code.

[Tom]

Very nice.

I find this type of data driven code generation is easier to manage using PUT statements instead of CALL EXECUTE. 

So rather than using code like this from Figure 9 construct a string to pass to CALL EXECUTE().

data _null_;
  length string $500;
  retain string;
  set metadata;
  by macro notsorted;
  if first.macro then do;
    string = cats('%',macro,'(',parm,'=',value);
  end;
  else string = cats(string,',',parm,'=',value);
  if last.macro then do;
    string = cats(string,')');
    put string=;
    call execute(string);
  end;
run;

You could use a simpler data step that just uses PUT statements to write the code to a file.  You can use %INCLUDE to have SAS execute the generated code.  

filename mcalls temp;
data _null_;
  file mcalls ;
  set metadata;
  by macro notsorted;
  if first.macro then  put '%' macro  / '(' @ ;
  else put ',' @ ;
  put  parm '=' value ;
  if last.macro then put ')' ;
run;
%include mcalls / source2 ;

Note that this method also eliminates the timing issues that can happen if you push a call to a macro with CALL EXECUTE() and that macro has conditional logic based on macro variable values that are calculated by the code the macro generates.  Those don't work right because the macro logic runs while the code is being pushed onto the stack to run after the data step finishes, but the generated code that is setting the values the conditional statements are testing have not run yet.

If you do use the CALL EXECUTE() method you can avoid the timing issue by adding %NRSTR() macro coding so that the macro call itself is pushed onto the stack and not the code that the macro generates.  Like this:

    string = cats('%nrstr(%',macro,')(',parm,'=',value);

[ArtC]

Thank you @Tom for touching on the use of the %NRSTR quoting function with CALL EXECUTE - nice explanation.  I have found that whether to use the PUT / %INCLUDE  vs CALL EXECUTE to build the call to mostly be a personal preference.  Some find it easier and others find it more confusing.  I would recommend that the reader have a good understanding of both techniques and use the one that seems easiest for them.  This paper does not deal with the specifics of CALL EXECUTE and as @Tom indicates there can be some 'subtle' gotchas with its use.  If his comment on %NRSTR was not just a reminder, you may want to read http://www2.sas.com/proceedings/sugi22/CODERS/PAPER70.PDF .as a starter for CALL EXECUTE.