The SAS Output Delivery System and reporting techniques

Supressing columns or rows in TABULATE based on value of class var

Reply
N/A
Posts: 0

Supressing columns or rows in TABULATE based on value of class var

I am using TABULATE for a report written to Excel where the MISSING option is needed since one of the class levels can have missing values. These obs are needed for the other class level vars' summary data. This leaves a set of columns (n sum rowpctsum) with all missing values for the class var having the missing values. Is there a style definition that can suppress just the columns from the missing value and keep the other columns for the non-missing values? Below is a skeleton of the code I am using as I have had to change var names to keep it from being recognized. The var rbrn can have missing values. This table has values of tbrn within group_id as the rows and rbrn as the columns. I realize that it is a lot to ask; any help you can provide would be greatly appreciated.

proc tabulate data=rpt_act_ missing;
class tbrn rbrn group_id;
var trvol rptvol tr_n rpt_n;
tables group_id*(tbrn all='Total'),
tr_n="# Tr"*sum=' '*f=comma12. tr_vol=' '*(sum*f=comma12. colpctsum)
rpt_n='# RtIDs'*sum=' '*f=comma12. (rbrn all='Rbrn Total')*rptvol=' '*(n='# Rpts'*f=comma12. sum*f=comma12.2 rowpctsum)/rts=14;
run;
SAS Employee
Posts: 174

Re: Supressing columns or rows in TABULATE based on value of class var

Posted in reply to deleted_user
It is not possible to suppress individual rows or columns in TABULATE. An entire table (logical page) of all missing values will be suppressed, though.

-- David Kelley, SAS
Frequent Contributor
Posts: 102

Re: Supressing columns or rows in TABULATE based on value of class var

Posted in reply to David_SAS
The ability to programmatically supress an entire row or column in PROC REPORT or PROC TABULATE seems to be a common request. I hope it's on the list of possible future enhancements.
N/A
Posts: 0

Re: Supressing columns or rows in TABULATE based on value of class var

Posted in reply to JackHamilton
You were replying while I was! So, not with PROC REPORT either. :-(
SAS Super FREQ
Posts: 8,864

Re: Supressing columns or rows in TABULATE based on value of class var

Posted in reply to deleted_user
Hi:
You can suppress a COLUMN on PROC REPORT output by using the NOPRINT option on the DEFINE statement. This option will suppress the column on EVERY row. When you have tabular output, whether it's PROC REPORT or PROC TABULATE, both procedures "want" the same number of columns on every report row. TABULATE won't let you hide columns, REPORT will -- but you have to hide the column on EVERY report row.

There is no equivalent to NOPRINT for a ROW. I have seen some folks fake out NOPRINT by making the background color and the foreground color of the row the same -- this leaves a space, but "blanks out" the report values on the row. And, this kind of cosmetic trickery may not work in all viewers -- for example, it might work in a browser, but not in Excel or Word or vice versa.

cynthia
N/A
Posts: 0

Re: Supressing columns or rows in TABULATE based on value of class var

Posted in reply to David_SAS
Thanks for looking. I knew it was only a slim (p<0.05) chance. Perhaps PROC REPORT may have a solution not involving many lines of code.
N/A
Posts: 0

Re: Supressing columns or rows in TABULATE based on value of class var

Posted in reply to deleted_user
Lucky for me, the columns that I want to hide are always the same. So with the new version of the tagsets, 1.86 I specified Hidden_columns='7-9' and that caused columns G, H, and I to be hidden!.

Now if I could only get the width to autofit!
Valued Guide
Posts: 2,177

Re: Supressing columns or rows in TABULATE based on value of class var

Posted in reply to deleted_user
beware of this as a solution, if you need security.
How important is it that these columns remain hidden?
How easy is it to "un-hide" columns in the generated excel?
If the whole column is to be suppressed because it is always empty, would it not be better merely to
not report these data? (just remove that part of the analysis.)

PeterC
N/A
Posts: 0

Re: Supressing columns or rows in TABULATE based on value of class var

In this particular case, these columns are always missing or zero so it is not important if they are unhidden. This is mainly for cosmetics.

It is caused by the way the data are structured. In the future, I may try to restructure things with a different approach.
Ask a Question
Discussion stats
  • 8 replies
  • 3147 views
  • 0 likes
  • 5 in conversation