topic Re: Reporting summarised data with Proc Tabulate in SAS Procedures

Reporting summarised data with Proc Tabulate

BruceBrad — Thu, 16 Oct 2014 07:06:04 GMT

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;

Re: Reporting summarised data with Proc Tabulate

BruceBrad — Mon, 28 Jun 2021 08:25:00 GMT

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?

Re: Reporting summarised data with Proc Tabulate

ChrisNZ — Fri, 02 Jul 2021 06:40:14 GMT

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.

Re: Reporting summarised data with Proc Tabulate

BruceBrad — Fri, 02 Jul 2021 07:08:11 GMT

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.

Re: Reporting summarised data with Proc Tabulate

ChrisNZ — Fri, 02 Jul 2021 07:16:18 GMT

what would the cell display?

Re: Reporting summarised data with Proc Tabulate

BruceBrad — Fri, 02 Jul 2021 07:41:55 GMT

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).

Re: Reporting summarised data with Proc Tabulate

ChrisNZ — Sat, 03 Jul 2021 02:30:40 GMT

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.