BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
8 REPLIES 8
David_SAS
SAS Employee
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
JackHamilton
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
You were replying while I was! So, not with PROC REPORT either. 😞
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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.
deleted_user
Not applicable
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!
Peter_C
Rhodochrosite | Level 12
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
deleted_user
Not applicable
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 8246 views
  • 0 likes
  • 5 in conversation