Example 15.8: Concatenating Output Data Sets: BY-Group Processing

Using the Output Delivery System

Example 15.8: Concatenating Output Data Sets: BY-Group Processing

Your data may be structured in such a way that the columns of an output table change over BY groups or from one section of output to another. This can occur in analyses that contain CLASS variables with differing values for some BY groups. This example demonstrates how you can write multiple ODS tables (possibly with different columns) to a single data set by using the MATCH_ALL option in the ODS OUTPUT statement.

Suppose that you want to analyze the following hypothetical data, which record the cell counts resulting from six types of blood tests administered at three laboratories. The values of method are not the same for each of the three laboratories. In the first laboratory, method = 1, 2, 3, 4; in the second laboratory, method = 2, 3, 4, 5; and in the third laboratory, method = 3, 4, 5, 6.

   data Tests;
      input lab method count @@;
      datalines;
   1 1 3  1 1 8  1 1 8  1 2 9  1 2 5  1 2 8
   1 3 9  1 3 8  1 3 4  1 4 5  1 4 8  1 4 7
   2 2 0  2 2 5  2 2 7  2 3 1  2 3 0  2 3 9
   2 4 9  2 4 6  2 4 9  2 5 9  2 5 0  2 5 1
   3 3 4  3 3 0  3 3 5  3 4 2  3 4 1  3 4 7
   3 5 1  3 5 6  3 5 6  3 6 6  3 6 2  3 6 1
   ;
   proc sort data=Tests;
      by lab;
   run;

In the following analysis, the MATCH_ALL option is omitted and the X'X matrix for each of the BY groups is output to a SAS data set. The columns of X'X depend on the levels of the CLASS variable. Therefore, the structure of the X'X matrix differs according to the level of the CLASS variable (method).

   ods output XpX=my1XpX;    /* Incorrect for this example */
  
   proc glm data=Tests ;
      class method;
      model count = method / xpx;
      by lab;
   quit;

   proc print data=my1XpX;
   title 'X''X Data Set, Omitting the MATCH_ALL Option';
   run;

The GLM procedure produces the following warning:

WARNING: Output object 'XPX' contains 1 column(s) that cannot be
         mapped to data set WORK.MY1XPX (there is no corresponding
         variable on the output data set). Use the MATCH_ALL option 
         to send each table to a separate data set.

NOTE: The above message was for the following by-group:
      lab=2

The PRINT procedure results, displayed in Output 15.8.1, show that the data set is missing columns for method=5 and method=6, which are in the second and third BY groups (methods performed in laboratories 2 and 3).

Output 15.8.1: PRINT Procedure: Omitting the MATCH_ALL Option

                                      X'X Data Set, Omitting the MATCH_ALL Option

Obs    lab    Parameter       Intercept        method_1        method_2        method_3        method_4           count

  1     1     Intercept              12               3               3               3               3              82
  2     1     method 1                3               3               0               0               0              19
  3     1     method 2                3               0               3               0               0              22
  4     1     method 3                3               0               0               3               0              21
  5     1     method 4                3               0               0               0               3              20
  6     1     count                  82              19              22              21              20             606
  7     2     Intercept              12               .               3               3               3              56
  8     2     method 2                3               .               3               0               0              12
  9     2     method 3                3               .               0               3               0              10
 10     2     method 4                3               .               0               0               3              24
 11     2     method 5                3               .               0               0               0              10
 12     2     count                  56               .              12              10              24             436
 13     3     Intercept              12               .               .               3               3              41
 14     3     method 3                3               .               .               3               0               9
 15     3     method 4                3               .               .               0               3              10
 16     3     method 5                3               .               .               0               0              13
 17     3     method 6                3               .               .               0               0               9
 18     3     count                  41               .               .               9              10             209

When multiple tables with different columns have the same name, as in this case, you can use the MATCH_ALL option to obtain correct results. The MATCH_ALL option creates a separate data set for each table. The initial data set name is specified after the equal sign, outside the parentheses, as follows:

   ods output XpX(match_all=list)=matrix;   /* Correct */

   proc glm data=Tests;
      class method;
      model count = method / xpx;
      by lab;
   quit;

In the ODS OUTPUT statement, the specified data set name is matrix. When a second output table is generated, the corresponding data set name is created by appending a `1' to the specified data set name. In this case, the second data set name is matrix1. Subsequent data sets are named matrix2 and so on.

The macro variable list (specified as MATCH_ALL=list) contains the list of data set names. Thus, ODS creates the macro variable &list = matrix matrix1 matrix2. Note that the use of a macro variable name with MATCH_ALL is optional.

The set of values contained in the macro variable list is used in the following DATA step to combine all of the individual data sets into one data set. Note that, when you refer to a macro variable, an ampersand (&) always precedes a macro variable name but is not part of the name.

   data my2XpX;
      set &list;
   run;

   proc print data=my2XpX;
   title 'X''X Data Set, Specifying the MATCH_ALL Option';
   run;

The final data set contains all of the results, including the results for method=5 and method=6 from the second and third laboratories. Output 15.8.2 displays the new data set.

Output 15.8.2: Print Procedure: Specifying the MATCH_ALL Option

                                    X'X Data Set, Specifying the MATCH_ALL Option

      P                    I
      a                    n            m            m            m            m                         m            m
      r                    t            e            e            e            e                         e            e
      a                    e            t            t            t            t                         t            t
      m                    r            h            h            h            h            c            h            h
      e                    c            o            o            o            o            o            o            o
  O l t                    e            d            d            d            d            u            d            d
  b a e                    p            _            _            _            _            n            _            _
  s b r                    t            1            2            3            4            t            5            6

  1 1 Intercept           12            3            3            3            3           82            .            .
  2 1 method 1             3            3            0            0            0           19            .            .
  3 1 method 2             3            0            3            0            0           22            .            .
  4 1 method 3             3            0            0            3            0           21            .            .
  5 1 method 4             3            0            0            0            3           20            .            .
  6 1 count               82           19           22           21           20          606            .            .
  7 2 Intercept           12            .            3            3            3           56            3            .
  8 2 method 2             3            .            3            0            0           12            0            .
  9 2 method 3             3            .            0            3            0           10            0            .
 10 2 method 4             3            .            0            0            3           24            0            .
 11 2 method 5             3            .            0            0            0           10            3            .
 12 2 count               56            .           12           10           24          436           10            .
 13 3 Intercept           12            .            .            3            3           41            3            3
 14 3 method 3             3            .            .            3            0            9            0            0
 15 3 method 4             3            .            .            0            3           10            0            0
 16 3 method 5             3            .            .            0            0           13            3            0
 17 3 method 6             3            .            .            0            0            9            0            3
 18 3 count               41            .            .            9           10          209           13            9

Chapter Contents
Previous
Next
Top