My boss asked me to remove values in certain cells generated by All (total) option in the Table statement of Proc Tabulate procedure. Would it be possible at all? If not, can you please advise what kind of procedures can be used in this case?
It really depends on what you need to remove and your particular data. You cannot generally change the data inside PROC TABULATE. The only thing you might be able to do (and I am NOT recommending this) is to design a user-defined format to turn the background color and foreground color to be the same color (which would essentially erase the contents of the cell). Of course, this technique would need a very specific user-defined format, a STYLE= override and you would have to be using ODS. And, the downside of this technique is that it really only works is if the number you want to "disappear" is unique on the whole report, because if the same number appeared in the same row or same column (depending on how you applied your format), you would end up blanking out that other number too. So -- possible, but I'd recommend looking at these other options in the order in which they are numbered.
Your other alternatives are
1) redesign the TABULATE report so the offending number does not show (does this possibly have to do with some percentages looking wrong on the ALL line? or are you passing data to TABULATE that already has a summary line -- do you really need the ALL?)
2) Switch the report to PROC REPORT, if possible, where you have more control over and could possibly suppress the numbers shown at a break.
3) Switch the report to a DATA _NULL_ report -- either writing the report to the LISTING window the "classic" way (Put @15 group @32 amount)
4) use DATA _NULL_ and ODS the "new" way: PUT _ODS_; This last method really includes an "A" and "B" choice -- you can use the default method to create a table from a data set or you can have your own custom TABLE template
PROC TABULATE is a wonderful and powerful procedure for summarizing huge amounts of data and with #1, you still use that power to do your number-crunching and table building. The last 3 choices are all capable of doing number-crunching, but not like TABULATE. The chances are good, that if you have a simple table, with not much crossing in the row and column dimension, that you could duplicate it easily with PROC REPORT or DATA _NULL_. But, depending on what you're doing with TABULATE, you may find that you still need TABULATE to do the number crunching to an output data set -- then you'd take the data set and send THAT data set to REPORT or DATA _NULL_ -- not the original one.
But, this is all VERY data and TABULATE process specific. Your best bet for help would be to contact Tech Support. They can look at your EXACT data and your EXACT code and help you come up with the best solution. My rule of thumb -- (for what it's worth -- I know you're dealing with your boss) -- has always been that if I feel the need to tamper with the numbers that come out of my procedure then the design of the report may need to be tweaked so the best display of the data is the result or, I may need to question my underlying assumptions about the data that I am sending to TABULATE (or any procedure) or there is some unhappy truth about the numbers that cannot be avoided.
To contact Tech Support, go to: http://support.sas.com and on the left side of the page, click on the link that says "Submit a Problem".
Thanks Cynthia! The reason that we'd like to remove values in certain cells is because those values are not meaningful and could potentially mislead others (they are the confidence intervals around a value that was forced purposefully). Unfortunately the number I want to remove is not unique and I cannot define a format using "proc format" to fix it. The tables have nested column headings which make transformation difficult. I have sent a request to the SAS technical support based on your advice and hopefully will get some assistance from them. Thanks again for your good advice!