how to average a computed column in SAS `proc report`

Question

In SAS proc report, a computed column is calculated row by row. This applies to the summary lines too, but that is not always wat you want.

As an example, take this study of the Body Mass Index in SASHELP.CLASS:

title Study Body Mass Index (BMI) by sex in class;
title2 Erroneously calculate average BMI from the average weight and height;
proc report data=sasHelp.class nowindows headline headskip split='*'
    style(summary) = {font_style=italic foreground=blue};

    where Name contains 'J'; * reduce the size to facilitate manual calculation ;
    columns sex name age height m weight kg BMI ;
    define sex    / group ;
    define age    / analysis mean format = 6.2;
    define height / analysis mean noprint;
    define weight / analysis mean noprint;
    define kg     / computed      format = 6.2 'Weight*(kg)';
    define m      / computed      format = 6.2 'Height*(meter)';
    define BMI    / computed      format = 6.2 'BMI*(kg/m²)';
    compute m;
        m = height.mean * .02540;
    endcomp;
    compute kg;
        kg = weight.mean * 0.45359237;
    endcomp;
    compute BMI;
        BMI = kg/m/m;
        if name eq '' then name = 'mean';
    endcomp;
    break after sex /summarize;
run;

It is wrong, because the BMI is not in the summary, i.e. for mean, is not the mean of the above BMI's, it is calculated from the height and weight left of it.

This is a correct calculation, summing BMI's and counting students manually.

title2 manually : summing BMI and counting students;
proc report data=sasHelp.class nowindows headline headskip split='*'
    style(summary) = {font_style=italic foreground=blue};

    where Name contains 'J'; * reduce the size to facilitate manual calculation ;
    columns sex name age height m weight kg BMI ;
    define sex      / group ;
    define age      / analysis mean format = 6.2;
    define height   / analysis mean noprint;
    define weight   / analysis mean noprint;
    define kg       / computed      format = 6.2 'weight*(kg)';
    define m        / computed      format = 6.2 'height*(meter)';
    define BMI      / computed      format = 6.2 'body mass*(kg/m²)';

    * initialize the sum and counter *;
    compute before sex;
        sumBMI = 0;
        count = 0;
    endcomp;

    compute m;
        m = height.mean * .02540;
    endcomp;
    compute kg;
        kg = weight.mean * 0.45359237;
    endcomp;
    compute BMI;
        if name eq '' then do;
            name = 'mean';

            * use the sum and counter *;
            BMI = sumBMI / count;
        end;
        else do;
            BMI = kg/m/m;

            * increase the sum and counter *
            sumBMI = sumBMI + BMI;
            count = count + 1;
        end;
    endcomp;
    break after sex /summarize;
run;

Is there a way to let proc report itself do the averaging correctly?

You could say I want to do analysis on a computed column, but you can only define a column an analysis column if it is on the input dataset.

Answer 1

• Create an alias column of an existing data set column.
• Redo the BMI computation for the alias column.
• In the summary line apply the alias column mean to the BMI column

In this example the column alias weight=bmiX is used.

proc report data=sasHelp.class nowindows headline headskip split='*'
    style(summary) = {font_style=italic foreground=blue};

    where Name contains 'J'; * reduce the size to facilitate manual calculation ;
    columns sex name age height m weight kg weight=bmiX BMI ;
    define sex    / group ;
    define age    / analysis mean format = 6.2;
    define height / analysis mean ;
    define weight / analysis mean ;
    define kg     / computed      format = 6.2 'Weight*(kg)';
    define m      / computed      format = 6.2 'Height*(meter)';
    define BMI    / computed      format = 6.2 'BMI*(kg/m²)';

*   define bmiX   / noprint;

    compute m;
        m = height.mean * .02540;
    endcomp;
    compute kg;
        kg = weight.mean * 0.45359237;
    endcomp;
    compute BMI;
        BMI = kg/m/m;
        if name eq '' then do;
          name = 'mean';
          BMI = bmiX;
        end;
    endcomp;

    compute bmiX;
      bmiX = kg/m/m;
    endcomp;

    break after sex /summarize;
run;