Removing merged cells in PROC TABULATE

[deleted_user]

Hello,

I have a little (but annoying) problem regarding the output SAS is generating when tabulating.

I'm using proc tabulate with code (simplified) : Class1, Class2 * Variable1.

The problem I have is that when exported to Excel, the first and second row are merged together as a result of "Class1" title showing. I would simply like to find a way to unmerge the cells in row 1, have the title and hopefully have the numbers in the row they belong.

At the moment, if I use the code Class1={LABEL=' '}, Class2 * Variable2, I get the output I want, but the "Class1" title is not showing...

Thanks in advance!

[Cynthia_sas]

Hi:
To get rid of that "double wide" row in TABULATE output, you are halfway there with suppressing the header for CLASS1 variable.

The next thing to try is:

[pre]
table class1=' ',
class2*variabl2 /
box=class1;

OR
table class1=' ',
class2*variabl2 /
box='MyClass1';
[/pre]

This will put your CLASS1 header or the string of your choice into the lovely empty box in the upper left hand corner of TABULATE output. This technique works better when you only have 1 class variable in the row area....since there's no way to "split" the box if you have 2 or more class variables nested in the row dimension.

cynthia

[deleted_user]

Thank you Cynthia.

The problem is, I'm actually using 2 classes, so the solution you gave is no good. I had though of it, but as you said, I couldn't find a way to split the box in 2.

Is there another way we can get rid of that "double wide" when 2 or more classes are used?

Thanks for your time.

[Cynthia_sas]

Hi:
At this point, you might find that PROC REPORT gives you nicer headers. But, it depends, finally on how complicated your TABULATE really is and how/whether it can be converted to PROC REPORT easily. See the example below...it's pretty simple and so could be done in either TAB or REPORT.

cynthia
[pre]
ods tagsets.excelxp file='flat_hdr.xml' style=sasweb;

proc tabulate data=sashelp.shoes;
where region in ('Asia', 'Canada', 'Pacific') and
product in ('Boot', 'Slipper', 'Sandal');
class region product;
var sales;
table region=' '*product=' ',
sales*(min mean max)/
box='Region and Product';
run;

proc report data=sashelp.shoes nowd;
where region in ('Asia', 'Canada', 'Pacific') and
product in ('Boot', 'Slipper', 'Sandal');
column region product sales,(min mean max);
define region / group;
define product / group;
define sales / 'Sales';
define min / 'Minimum';
define mean / 'Average';
define max / 'Maximum';
run;
ods _all_ close;
[/pre]

[deleted_user]

I cannot seem to find a way to use multiple dimension in the columns in the PROC REPORT.

Example : I want to have a class (with 3 subcategories) and then all the variables. So there would be Amortization in years ["<25 years" (Variable1, Variable2 Variable3) / "25years <30 years" (Variable1, Variable2, Variable3) / ">30 years" (Variable1, Variable2, Variable3)]

Or in tabulate environment (columns only)

Amortization*(Variable1 Variable2 Variable3)

Also, it is important to know that I am using computed variables (I had to create 2 tabulate procedure to do that). Is Proc Report the right choice for me? If not, is there a way I can get rid of that "double wide".

Again, thanks for the help.

[deleted_user]

Or in tabulate environment (columns only)

Amortization*(Variable1 Variable2 Variable3)

Also, it is important to know that I am using computed variables (I had to create 2 tabulate procedure to do that). Is Proc Report the right choice for me? If not, is there a way I can get rid of that "double wide".

Again, thanks for the help.

PS : Sorry for double-posting, I think there's a problem as my whole message did not get sent the first time)

[Cynthia_sas]

Hi:
PROC REPORT in my example, was a very simple report. I did not have any "ACROSS" usage items. But, in the program below, I switched to SASHELP.PRDSALE, because it only has 3 countries and 2 prodtypes and 2 region values, so it made a smaller table ... but you can see that with COUNTRY as an "ACROSS" usage item on the report, I get the Min, Mean and Max values for ACTUAL nested underneath each country. PROC REPORT in my example, is using aliases for the ACTUAL variable -- I'm just using ACTUAL 3 times on the report, but I could have as easily done COUNTRY,(VAR1 VAR2 VAR3) in my column statement. Just note that REPORT syntax is different from TABULATE syntax....and that in either case, COUNTRY had to be defined to REPORT as an ACROSS usage.

I don't know whether PROC REPORT is the right choice for you. Sometimes with TABULATE, you have to "pre-calculate" your computed variables before the TABULATE step and I assume this is what you've done when you say you've made "computed" variables with TABULATE. You may not need to do that with REPORT because REPORT allows computed report items in the syntax of PROC REPORT.

Both TABULATE and REPORT have strengths and weaknesses -- the "double wide" row is one glitch of TABULATE that you don't have with REPORT. But, REPORT is not as elegant or concise as TABULATE in how you express dimensions or nested hierarchies in multiple dimensions. On the other hand, REPORT makes up for that by having COMPUTE blocks and the CALL DEFINE capability and the LINE statement that go a long way toward making REPORT more flexible than TABULATE. On the other, -other- hand, TABULATE has that lovely ALL universal CLASS variable. On the other, -other-, -other- hand, if you do have ACROSS variables with PROC REPORT and you want to create a computed variable inside the PROC REPORT syntax, you have to use absolute column names (_c2_, _c3_, etc).

In this case, it really depends on the structure of the data and what your final report needs to look like...and to some extent, how comfortable you are with REPORT and TABULATE.

cynthia

[pre]
ods tagsets.excelxp file='across_report.xml' style=sasweb;
proc report data=sashelp.prdsale nowd;
column prodtype region country,(actual actual=aavg actual=amax);
define prodtype/ group;
define region / group;
define country/ across 'Country Actual Sales';
define actual / min 'Mininum';
define aavg / mean 'Average';
define amax / max 'Maximum';
run;
ods _all_ close;
[/pre]

[deleted_user]

Cynthia, you are the best. You answered my question and gave even more info. As stated earlier, your expertise is much appreciated.

I have a -final- quick question on the PROC REPORT. Is there an easy way to mimic the ALL universal CLASS from PROC TABULATE into PROC REPORT?

I have read that it is possible to make total and subtotal rows, but the code is very complicated for just an additional line in the table.

I'm beginning to think that if there's no easy way I can get those subtotals and totals in the PROC REPORT table, I'd be better off living with the gap of PROC TABULATE.

Again, thanks for your time.

Samuel

[Cynthia_sas]

Hi:
Well, ALL is a great tool. But I don't find subtotals and totals so daunting, once you understand how they work.

Proc REPORT needs to know 2 things: what variable value will control the placement of the summary line and what you want to have happen on the summary line (see numbers or not).

In my code below, PRODTYPE is a GROUP variable and so BREAK AFTER PRODTYPE is the statement that will put a subtotal summary line after each unique value of PRODTYPE. The statistics I get on the summary line will come from each DEFINE statement for my numeric variables. The COMPUTE AFTER PRODTYPE is totally optional -- I like a little space after my subtotals, so I put it in with the LINE statement.

The RBREAK AFTER statement is doing the GRAND TOTAL summary line at the end of the report. If I did both subtotals and grand totals with TABULATE, I'd need 2 ALLs -- with PROC REPORT, I need 2 different statements. Both statements in PROC REPORT need the SUMMARIZE option explicitly specified on the statement -- like you have to tell PROC REPORT that you mean business and want to see numbers. Otherwise, if you don't have the SUMMARIZE option, the BREAK and RBREAK statements are not very useful.

TABULATE does allow you to use ALL in multiple dimensions -- not just the ROW dim, but the COLUMN dim as well. When you use ACROSS items with PROC REPORT, that's where the equivalent of ALL might come in handy. In the report below, I've done a SUM of all the ACTUAL variable values (for ALL the countries) by using an alias for ACTUAL outside the parentheses that belong to the ACROSS nesting. So when I have ACTUAL=TOTACT in the COLUMN statement, I am placing a summary column at the far right of the report table. (and doing the same for the PREDICT variable using TOTPRD as the alias)

You'll get a better feel for what is happening if you run the program and examine the output. The BREAK code is not complicated if you only want one line. Customizing that line or adding a skipped line for readability adds a bit more complexity, but not too much. PROC REPORT is more verbose than PROC TABULATE, but it is also less "algebra-ish".

hope this helps,
cynthia

[pre]
proc report data=sashelp.prdsale nowd;
column prodtype region country,(actual predict)
("For All Countries" actual=totact predict=totprd);
define prodtype/ group;
define region / group;
define country/ across 'Country Actual and Predict';
define actual / sum 'Actual';
define predict / sum 'Predict';
define totact/ sum 'Sum ACTUAL';
define totprd/ sum 'Sum PREDICT';
rbreak after / summarize;
break after prodtype / summarize;
compute after prodtype;
line ' ';
endcomp;
run;
[/pre]

[deleted_user]

Thank you very much, I will take that into consideration when I finally decide wether to go PROC REPORT or PROC TABULATE.

Your help is gold to me... You are making me a better SAS user and I appreciate it :).

[soosas]

Hi, found this thread in a Google search and saw it's been a while since this thread updated. Found that since SAS 9.3 the NOCELLMERGE option is now available for PROC TABULATE:

I believe adding NOCELLMERGE to the TABLE options under PROC TABULATE to prevent merging of cells in ODS destinations. "Without this option, table cells are merged with the variable header and the first variable value in the row dimension." See below for reference.

proc tabulate data=sashelp.class;
   class sex;
   table sex, n / nocellmerge;
run;

Usage Note 57182: The NOCELLMERGE option prevents the merging of cells in PROC TABULATE output in the ODS destinations:

http://support.sas.com/kb/57/182.html