RunBY macro: Repeatedly run or add BY processing to any macro, procedure, or special code

Contents:

Purpose / History / Requirements / Usage / Details / Limitations / Missing Values / See Also

PURPOSE:

Many macros, and some procedures, were not written with the built-in capability to perform their analysis or operation separately on each of multiple blocks of observations in the input data set. For example, if your data set contains a block of observations for each of several countries or regions or conditions (such as gender or race), most SAS procedures allow you to run the procedure a single time with a BY statement telling the procedure to do the analysis or operation on each block of observations (called BY groups). The RunBY macro makes it possible to do the same with most macros and procedures that do not have this built-in BY processing capability. More generally, the macro can be used to run any code as indicated by the contents of each observation in a specified data set.

HISTORY:

The version of the RunBY macro that you are using is displayed when you specify version (or any string) as the first argument. For example:

    %RunBY(version)

The RunBY macro always attempts to check for a later version of itself. If it is unable to do this (such as if there is no active internet connection available), the macro will issue the following message:

   NOTE: Unable to check for newer version

The computations performed by the macro are not affected by the appearance of this message.

Version	Update Notes
1.1	Added lvlquote= and print=
1.0	Initial coding

REQUIREMENTS:

Only Base SAS^® is required.

USAGE:

Follow the instructions on the Downloads tab of this sample to save the RunBY macro definition. Replace the text within quotation marks in the following statement with the location of the RunBY macro definition file on your system. In your SAS program or in the SAS editor window, specify this statement to define the RunBY macro and make it available for use:

   %inc "<location of your file containing the RunBY macro>";

After running the above statement, the RunBY macro is ready for use. However, before you call the macro, you must create a simple macro, named code, that contains the code that you want to apply to each BY group. See Details below and the Results tab for examples.

The following parameter is required when using the RunBY macro:

by=variable(s)

List the variables that define the BY groups. Variables can be numeric or character. None of the values in character variables can contain the percent (%) or ampersand (&) character or contain unmatched quotation marks (' or "). A BY group is one combination of values of all of the variables specified in by=.

The following parameters are optional:

data=data-set-name

Specify the name of a data set that contains the by= variables with the desired levels defining all BY groups. The code that you supply in the code macro will be run once for each BY group defined by the by= variables in this data set. This data set might or might not be the data set that is analyzed by the code that you supply in the CODE macro. If data= is not specified, then the last created data set is used.

order=internal|formatted|data|freq

Specifies how the by= variable values should be ordered. internal uses the unformatted variable values. formatted uses the formatted variable values if a format is assigned. data orders the values as they are in the data= data set. freq orders the values in decreasing frequency as found in the data= data set. The order of the by= variable values in the data= data set do not have to be in the order specified in order= unless order=data is specified.  The default is order=internal.

lvlquote=yes|no

Values of character by= variables, represented by the _LVL macro variables, can be inserted into the supplied code with surrounding double quotation marks (lvlquote=yes) or without quotation marks (lvlquote=no), depending on what is needed in the supplied code. See the Example in Details below. The default is lvlquote=yes.

print=yes|no

Displayed results produced from running the supplied code can be displayed (print=yes) or suppressed (print=no). The default is print=yes.

DETAILS:

The RunBY macro runs the code that you supply once for each BY group as defined by the combinations of the by= variable values found in the data= data set. For example, if you specify by=gender, then the code is run once for males and once for females. Unlike with the BY statement used in the DATA step or procedures, it is not necessary to sort the data= data set by the by= variables before using the RunBY macro.

 

User-defined macro, code, and special macro variables _BYx, _LVLx, and _BYlabel

You specify the code that you want to run on each BY group in a simple macro called code. In order for your code to select one BY group at a time, it should contain a subsetting condition using a special pair of macro variables, _BYx and _LVLx, where x is a number representing the x-th variable specified in by=. Typically, your code will do this by containing a WHERE statement or clause that uses these macro variables. If a by= variable is character, it is not necessary to specify quotation marks around _LVLx in the condition. Additionally, the macro variable, BYlabel, is available for labeling the displayed results with the BY group definition. BYlabel can be used in a TITLE, FOOTNOTE, or other appropriate statement or option.

Example

Following is an example where the BY groups are defined by the single character variable, Gender, that has values Female and Male in data set MyData. You want to run a macro (not shown) called MyMacro for each level of Gender. You precede your code with a %macro code(); statement, and follow it with a %mend; statement as shown. Assuming that MyMacro does not have built-in BY processing capabilities, an analysis on one Gender level needs to subset the data and then run MyMacro on that subset. The subsetting is easily done with a WHERE statement in a DATA step to create the subset. A WHERE clause in the SET statement could be used instead. When the RunBY macro runs, it replaces the macro variables, _BY1 and _LVL1, with Gender="Female" for one analysis, and then with Gender="Male" for the second analysis. Note that you do not need to specify quotation marks around _LVL1 in the WHERE statement condition since this is done for you by lvlquote=yes. The results are labeled with a title line showing the appropriate one of these two BY group definitions. This assumes that MyMacro itself does not overwrite any prior title. If so, perhaps a FOOTNOTE statement could be used instead. Note the use of the ampersand (&) character to allow resolution of the _BY1, _LVL1, and BYlabel macro variables.

    %macro code();
      data subset;
        set MyData;
        where &_BY1 = &_LVL1;
        run;
      title "&BYlabel";
      %MyMacro(data=subset)
      title;
    %mend;
    %RunBY(data=MyFullData, by=Gender)

If MyMacro is written to accept data set options in its data= parameter, the following could also work:

    %macro code();
      %MyMacro( data=subset(where=(&_BY1 = &_LVL1)) )
    %mend;
    %RunBY(data=MyData, by=Gender)

If there are multiple by= variables, for example by=gender race, then the WHERE statement would include a second condition as below, and so on, for any number of by= variables.

    where &_BY1 = &_LVL1 and &_BY2 = &_LVL2;

LIMITATIONS:

Unmatched quotation marks, either an unmatched single quotation mark (') or an unmatched double quotation mark ("), and the ampersand (&) and percent (%) characters are not allowed in the values of any of the by= variables and might cause errors. If ampersand (&) and percent (%) characters are detected, a WARNING message is printed in the log and the BY group is skipped.

MISSING VALUES:

Missing values in the by= variables are treated as additional levels.

SEE ALSO:

Another way to run macros, procedures, or special code is using the CALL EXECUTE function in the DATA step. This method is shown in SAS Note 46847 to fit a series of logistic models using, in turn, one variable from a set of predictor variables.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

In addition to the examples below, you can see examples of using the RunBY macro to add BY group processing to other macros. See the example titled "BY group processing" in the documentation of the following macros.

• Margins macro (SAS Note 63038)
• MAGREE macro (SAS Note 25006)
• NLMeans macro (SAS Note 62362)
• NLEst macro (SAS Note 58775)
• RsquareV macro (SAS Note 60162)
• MultAUC macro (SAS Note 64029)

EXAMPLE 1: Run PROC GAMPL on BY groups

Since PROC GAMPL is a procedure that does not support a BY statement, the RunBY macro can be used to run the procedure on BY groups in the data. The following uses the diabetes data in the example titled "Nonparametric Logistic Regression" in the PROC GAMPL documentation (SAS Note 22930). The statements below include a WHERE statement that subsets the input data set to each BY group in turn. An ODS SELECT statement is included to limit the displayed results to the tests of the splines.

    %macro code();
      proc gampl data=DiabetesStudy seed=12345; where &_BY1=&_LVL1; 
        model diabetes(event='1')=spline(Glucose) spline(Pressure)/dist=binary; 
        ods select SmoothingTests;
        title "&BYlabel";
        run;
      title;
    %mend;
    %RunBY(data=DiabetesStudy, by=test)

The results for each level of TEST show that the effect of Glucose is significant in both BY groups but the effect of Pressure is significant in only the TEST=1 BY group.

EXAMPLE 2: Run multiple one-variable models and accumulate estimates

Suppose you want to fit a series of logistic models using hypothetical data set MyData. MyData contains variables V1, V2, V3, V4, Y, and possibly several other variables. One variable from a specified list of variables should be used as the single predictor in each model on response variable Y. Some variables might be categorical (numeric- or character-valued) and others continuous. The final result should be a single data set containing the parameter estimates from the models. Displayed results from each model fit are to be suppressed.

The following statements create data set VarsRef that contains, in variable VAR, the list of variables to be used as the predictor. Variable CAT indicates whether the variable is categorical (1) or not (0). Variable REF contains the desired reference level for the categorical variables. Continuous variables have no reference level and are assigned a missing value. The four observations of the data set define the four models to be run.

The CODE macro is defined next. This macro is run repeatedly when the RunBY macro is called. It contains the statements to be run for each observation of the data= data set, VarsRefs. For each observation, the values of variables VAR, REF, and CAT are available in the _LVL1, _LVL2, and _LVL3 macro variables. For example, for the first row, _LVL1=v1, _LVL2=1, and _LVL3=1. Because lvlquote=no is specified in the RunBY macro call, the values do not include enclosing quotation marks.

In the PROC LOGISTIC code to be run, _LVL3 is used in a %IF macro statement to determine if a CLASS statement is needed for the current variable. If so, the variable name, in _LVL1, is inserted into the CLASS statement and its reference level, in _LVL2, is inserted into the REF= option. For each run, the table of parameter estimates is saved in a data set named using the variable name in _LVL1 prefixed by PE_. To suppress the displayed results from the multiple LOGISTIC runs, print=no is specified in the RunBY call.

Finally, the RunBY macro is called. Specifying VarsRef in data= tells RunBY to run the CODE macro once for each observation in VarsRef. The by= specification tells RunBY to create macro variables containing each name. Specifically, it creates macro variable _BY1 with value var, macro variable _BY2 containing ref, and _BY3 containing cat. The values of these variables in the current observation of the data= data set are placed in macro variables _LVL1, _LVL2, and _LVL3. The BYLABEL macro variable, which combines all the by= variable names and values for the current observation, is also available. For the first observation, the BYlabel macro variable contains VAR=v1 REF=1 CAT=1. All these macro variables are available for use in the CODE macro. However, for this example, the _BYx and BYlabel macro variables are not needed.

    data VarsRefs;
       input var $ ref $ cat;
       datalines;
    v1 1 1
    v2 a 1
    v3 . 0
    v4 . 0
    ;
    %macro code;
       proc logistic data=MyData;
       %if &_LVL3=1 %then %do;
         class &_LVL1(ref="&_LVL2")/param=ref;
       %end;
       model y(event='1') = &_LVL1;
       ods output parameterestimates=pe_&_LVL1; 
       run;
    %mend;
    %RunBY(data=VarsRefs, by=var ref cat, lvlquote=no, print=no)

Following the RunBY call, the parameter estimates data sets are concatenated and the resulting data set is displayed. The specification, PE_:, is interpreted as all data sets beginning with PE_ in their names.

    data all; set pe_:; run;
    proc print; run;

EXAMPLE 3: Run code for each combination or permutation of variable values

The RunBY macro can also be helpful when you need to run some code for every possible permutation or combination of values in a particular variable in the data. Similarly, it can be used to run code for each unique, observed, combination of levels of two or more variables in the data.

The DATA step below creates data set SCHOOL. Suppose you want to test the equality of COUNT variances for every possible pair of STYLE using PROC TTEST. TTEST requires a binary CLASS variable indicating the two levels to be compared. PROC SQL is used to produce a data set (PAIRS), containing two variables, STYLE1 and STYLE2, defining the three possible combinations of the three STYLE levels.

     data school;
        length Program $ 9;
        input School Program $ Style $ Count @@;
        datalines;
     1 regular   self 10  1 regular   team 17  1 regular   class 26
     1 afternoon self  5  1 afternoon team 12  1 afternoon class 50
     2 regular   self 21  2 regular   team 17  2 regular   class 26
     2 afternoon self 16  2 afternoon team 12  2 afternoon class 36
     3 regular   self 15  3 regular   team 15  3 regular   class 16
     3 afternoon self 12  3 afternoon team 12  3 afternoon class 20
     ;
     proc sql noprint;
       create table pairs as select distinct
         a1.style as style1,  a2.style as style2
         from school as a1, school as a2 
         where style1 < style2;
       quit;
     proc print;
       run;

Below is data set PAIRS.

Data set PAIRS can now be used in the RunBY macro to repeatedly call PROC TTEST to compare the variances of each possible pair of styles. This is done by including a WHERE statement in the TTEST step to select one pair in each run. Assume that only a data set of the relevant test results, and no displayed results from PROC TTEST, is desired. This can be done by including an ODS OUTPUT statement that saves the Equality table that contains the equal variances test. Because this table does not identify the two CLASS levels being compared, the DATA step below adds a variable, PAIR, that contains the two levels. Then PROC APPEND is used to accumulate the tables from the analyses on the separate pairs into a single data set, VARS, of test results.

     %macro code;
       proc ttest data=school; 
          where style=&_lvl1 or style=&_lvl2;
          class style; var count;
          ods output equality=ev;
          run;
       data ev; set ev; 
          length pair $20.;
          pair=catx(" ",&_lvl1,&_lvl2);
          run;
       proc append data=ev base=vars; 
          run;
     %mend;
     %runby(data=pairs, by=style1 style2, print=no)

     proc print;
        id pair;
        run;

Final data set VARS is displayed below.

Note that to obtain all possible permutations, rather than combinations, of STYLE levels for use in RunBY, you can simply change the WHERE clause in the PROC SQL step above to where style1 ne style2. Suppose that instead of combinations or permutations of values in one variable, you want to run code for each observed combination of values in two or more variables. A suitable data set for use in RunBY can be obtained using PROC SORT with the NODUPKEYS option. For example, these statements produce data set COMBOS containing the observed combinations of STYLE and SCHOOL values.

     proc sort data=school out=combos nodupkeys;
        by style school;
        run;

Right-click the link below and select Save to save the RunBY macro definition to a file. It is recommended that you name the file RunBY.sas.

Download and save RunBY.sas

The RunBY macro allows you to run macros, procedures, or other code on blocks of observations in the same way as the BY statement operates in procedures. One call of the RunBY macro runs the supplied code as indicated by the contents of each observation in a specified data set.

Type:	Sample
Topic:	SAS Reference ==> Macro Analytics ==> analytics

Date Modified:	2023-10-09 12:12:23
Date Created:	2020-07-06 16:05:50

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	OpenVMS VAX
		z/OS
		z/OS 64-bit
		Microsoft Windows 8.1 Enterprise 32-bit
		Microsoft Windows 8 Pro 32-bit
		Microsoft Windows 8 Pro x64
		Microsoft Windows 8 Enterprise x64
		Microsoft Windows 8 Enterprise 32-bit
		OS/2
		Microsoft® Windows® for x64
		Microsoft Windows XP 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Macintosh
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows Server 2019
		Microsoft Windows Server 2012 Std
		Microsoft Windows Server 2012 R2 Datacenter
		Microsoft Windows Server 2008 for x64
		Microsoft Windows Server 2003 for x64
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows NT Workstation
		Microsoft Windows 2000 Professional
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 95/98
		Microsoft Windows 10
		Microsoft Windows 8.1 Pro x64
		Microsoft Windows 8.1 Pro 32-bit
		Microsoft Windows 8.1 Enterprise x64
		Microsoft Windows XP Professional
		Microsoft Windows Server 2016
		Microsoft Windows Server 2012 R2 Std
		Microsoft Windows Server 2012 Datacenter
		Microsoft Windows Server 2008 R2
		Microsoft Windows Server 2008
		Windows 7 Enterprise 32 bit
		Windows 7 Enterprise x64
		Windows 7 Home Premium 32 bit
		Windows 7 Home Premium x64
		Windows 7 Professional 32 bit
		Windows 7 Professional x64
		Windows 7 Ultimate 32 bit
		Windows 7 Ultimate x64
		Windows Millennium Edition (Me)
		Windows Vista
		Windows Vista for x64
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		IRIX
		Linux
		Linux for x64
		Linux on Itanium
		OpenVMS Alpha
		OpenVMS on HP Integrity
		Solaris
Solaris for x64
Tru64 UNIX