Reporting summarised data with Proc Tabulate

[BruceBrad]

I often find that I'm drawing on the formatting features of Proc Tabulate to neatly present data which have already been summarised. That is, I just want to report single values in the table cells rather than summary statistics like the mean. I can, of course, do this by just presenting the mean (or the min, or the max etc) of the value. However, I would like to be able to double-check that I do indeed only have one value in the cell. Any suggestions on how to do this?

More concretely, consider the following program which tabulates average income by sex and age. The first table works fine as a way to present these data. I'm using 'min' not because I want the minimum, but simply to report the (single) cell value. In the second table, I've introduced a mistake. I'm only tabulating by age, but there are two records in every cell in this case.

What I would like would be some way to flag that the second table is not valid for my data. A crude way is to tabulate the N function for each cell and check this for all 1 s - but this produces an additional ugly table.

Ideally I would like to do with an 'only' function. This would return a missing value if the values in the cell had a positive variance (or range), and would return the mean (or min or max) if the variance was zero. I don't think this is possible in SAS.

Any other suggestions? Or should I use some totally different approach to tabulating already summarised data. Proc report is naturally oriented towards listing cases, but my understanding is that it doesn't have the flexibility of tabulate for rearranging row and columns.

data test;

input sex $ age $ avg_income;

cards;

M Y 100

M O 210

F Y 66

F O 33

;

run;

proc tabulate data=test;

  class sex age;

  var income;

  table min=""*avg_income*sex,age;

  table min=""*avg_income*age;

run;

[BruceBrad]

Bumping this. It's 7 years later and I would still like to be able to do this! Any progress on using custom functions in proc tabulate?

[ChrisNZ]

Your constraints are unclear.

Can you run proc tabulate twice? Once with statistics N and option OUT=, to check the underlying data, and then for the report?

proc report is typically more flexible than proc tabulate, not less.

[BruceBrad]

Doesn't always work for data structure I'm using. Eg often I will have a table where some cells are constants (and I'd like to double-check this) while others are variable and I want to calculate the mean. It would be neat if I could write a cell function for this.

[ChrisNZ]

what would the cell display?

[BruceBrad]

One way to summarise: If the variance is positive, show missing, if the variance is zero, show the mean (or the first, last - all the same).

[ChrisNZ]

The only way is to pre-summarise and then set the values in a data step using the logic you describe.

Then you display them as you do now.