Macro TableDrivenMacro

From sasCommunity

http://www2.sas.com/proceedings/forum2008/102-2008.pdf

SGF2008.102

[edit] Using a SAS® Table to Drive Any Macro

Mike Whitaker, CTB/McGraw-Hill, Monterey, CA

ABSTRACT

This paper presents a macro that repetitively calls a target macro using data read from a SAS® table as the parameters. The macro is completely general, and can be used at any point in a SAS® program, making it a useful utility for solving a wide variety of programming problems. Five applications given are: creating macro symbol lists, creating simple SAS® library reports, preventing unintentional inclusion of a new column to a SAS® table, systematic renaming of columns and using data from an Excel worksheet.

 /* ================================================================== *
<header>
<company>CTB/McGraw-Hill</company>
<copyright>2008</copyright>
<name>TableDrivenMacro</name>
<author>Mike Whitaker</author>
<description>
The Macro reads a SAS Table and uses the values of
a selected set of columns for that table to
execute a macro with those values, one execution per
row of the table.
</description>
<usage>
Use this whenever you want to execute a macro a number of times, and the
data to control the execution can be retrieved from an existing table
</usage>
<remarks> <ol class="decimal">
<li>The Macro's Parameters and the Table's Columns must be the
Same Names</li>
<li>Any SASTable will work</li>
<li>Any Dataset Options are permitted</li>
<li>Any Macro is permitteded</li>
<li>The Macro can call itself</li>
<li>The Table and Macro Must exist beforehand</li>
</remarks>
<history>
<action initials="MDW" date="Oct 12 2006"> Macro Created</action>
<action initials="MDW" date="Jan 13 2008"> Made Tableid,
                             libref and RC Local Symbols</action>
</history>
</header>
* =================================================================== */
 /* ================================================================== *
<comment>
<code type="Macro"> TableDrivenMacro</code>
<param name="Table" default="">Any SAS Table
                               with any DataSet options</param>
<param name="Macro" default="">Any SAS Macro without the %</param>
<param name="Parms" default="">The Column Names to use</param>
<example>%TableDrivenMacro(<br>
Table=myTable,<br>
Macro=MyMacro,<br>
Parms=Col1 Col2)<br></example>
</comment>
* =================================================================== */
%macro tabledrivenmacro(
Table = ,
Macro = ,
Parms =
)/ des = 'read table for each row call macro';

%*****************************************************;
%* The library with the SAS dataset that has the ;
%* Data for the macro variables used by the macro ;
%* within the do loop below ;
%*****************************************************;
%local Tableid Rc libref;
%if %qscan(&Table,2,.) ~= %then %do;
    %let libref = %qscan(&Table,1,.);
         %if (%sysfunc(libref(&libref.)) ~= 0) %then %do;
         %put %sysfunc(sysmsg());
         %Return;
         %end;
    %end;

%*********************************************;
%* open the SAS Table of macro variables ;
%*********************************************;
%let tableid= %sysfunc(open(&Table,is));
%if (&tableid = 0) %then %do;
    %put %sysfunc(sysmsg());
    %Return;
    %end;

%********************************************;
%* State that we want all of the values ;
%* of on a row ;
%********************************************;
%syscall set(tableid);

%*******************************************;
%* Read the first observation ;
%*******************************************;
%let rc=%sysfunc(fetch(&tableid));
%if (&rc ~= 0) %then %do;
    %put %sysfunc(sysmsg());
    %let rc = %sysfunc(close(&tableid));
    %Return;
    %end;

%********************************************;
%* Do until end of file found ;
%********************************************;
%do %while(&rc=0);

     %*********************************************;
     %* Use the Parameters List to strip off the ;
     %* White Space ;
     %*********************************************;
     %local ThisParm parmstring J;
     %let parmstring=;
     %let j = 1;
     %let Thisparm = %qscan(&Parms,&j);
     %do %while(%quote(&thisparm) ~=);
         %let &thisparm = %sysfunc(strip(&&&thisparm));
         %let parmstring = &parmstring%str(,)&thisparm=&&&thisparm;
         %let j = %eval(&j+1);
         %let Thisparm = %qscan(&Parms,&j);
         %end;

     %***********************************************;
     %* Nibble off the first comma and put in ;
     %* Parentheses. ;
     %***********************************************;
     %let parmstring = (%substr(&parmstring,2));

     %*********************************************;
     %* Call the macro you want to execute using ;
     %* this parameter string ;
     %*********************************************;
     %&Macro&parmstring

     %*******************************************;
     %* Now Read the next record from the Table ;
     %* and do it again ;
     %*******************************************;
     %let rc=%sysfunc(fetch(&tableid));
     %end;

     %*******************************************;
     %* close the table ;
     %*******************************************;
     %if (&rc ~= -1) %then %do;
         %put %sysfunc(sysmsg());
         %Return;
         %end;
     %let rc = %sysfunc(close(&tableid));
%mend;