When i save proc tabulate output in HTML or Excel format. It merges first row of table (excluding header). Is there any way to get rid of it?
See the sample sas code below :
Data test;
Input T1 T2 T3 T4 T5 Age BU;
Cards;
1 5 2 3 4 3 3
4 5 2 1 2 1 3
3 4 4 3 2 3 2
4 3 2 5 3 3 3
1 2 4 2 1 2 2
;
Run;
Proc Tabulate Data = test Out=test1;
Class Age;
Var T1;
Table Age, T1 * (N COLPCTN);
Run;
Hi:
The first "row" of the table is actually what TABULATE defines as the ROW HEADER area. The big empty box at the top of the ROW HEADER area is how TABULATE distinguishes between the COLUMN headers (in your table the N and COLPCTN and the ROW HEADERS (each value for AGE and the header for AGE).
With just 1 variable in the ROW HEADER area, the way to fix it is pretty simple, you can just move the header for AGE up into the BOX and suppress the normal placement of AGE under the box:
Table Age=' ', T1 * (N COLPCTN) / box='Age';
Then if you want to change the vertical justification for the new header in the Box area, you need to only make 1 more change:
Table Age=' ', T1 * (N COLPCTN) / box={label='Age' style={vjust=b}};
cynthia
BTW, not sure why you're using OUT= to make a SAS dataset from your results, the structure of the output data for this example might mimic the TABULATE report structure, with some extra variables added, but this is not always the case.
Hi:
The first "row" of the table is actually what TABULATE defines as the ROW HEADER area. The big empty box at the top of the ROW HEADER area is how TABULATE distinguishes between the COLUMN headers (in your table the N and COLPCTN and the ROW HEADERS (each value for AGE and the header for AGE).
With just 1 variable in the ROW HEADER area, the way to fix it is pretty simple, you can just move the header for AGE up into the BOX and suppress the normal placement of AGE under the box:
Table Age=' ', T1 * (N COLPCTN) / box='Age';
Then if you want to change the vertical justification for the new header in the Box area, you need to only make 1 more change:
Table Age=' ', T1 * (N COLPCTN) / box={label='Age' style={vjust=b}};
cynthia
BTW, not sure why you're using OUT= to make a SAS dataset from your results, the structure of the output data for this example might mimic the TABULATE report structure, with some extra variables added, but this is not always the case.
ods html body='C:\Users\Desktop\table.html';
Proc Tabulate Data = test Out=test1;
Class Age;
Var T1;
Table Age=' ', T1 * (N COLPCTN)/box={label="Age"};
Run;
ods html close;
Thanks a ton! It works like a charm. I have tried the same in two row headers, it's not working. Did i miss something?
Proc Tabulate Data = test Out=test1;
Class Age BU;
Var T1;
Table (Age=' ' * BU=' '), T1 * (N COLPCTN) / box={'Age';'BU'};
Run;
Thanks in anticipation!
Box option wants a single string which by default it will attempt to center horizontally in the box. You may have to play around with padding spaces to get a pretty alignment:
Box='Age BU'
Hi:
As I explained, the Box technique works best if you only have 1 ROW HEADER item. With 2 items, the spacing in the BOX option label won't ever "split" into separate cells, if that is what you are trying to achieve.
cynthia
Thank you for your reply. Yes, i have run the code and it didn't split into two cells. Is there any way we can split into separate cells?
Hi:
Perhaps create the data/column percents with one procedure like PROC TABULATE or PROC FREQ and then use PROC REPORT which does not have the BOX area and which gives each column it's own "cell". Compare the output from these 2 programs. It's much simpler in terms of statistics, than your example, but it's only to show the heading differences for essentially the same table done using each procedure.
Cynthia
ods _all_ close;
title; footnote;
ods html file='c:\temp\compare_tab_rep.html';
proc tabulate data=sashelp.shoes;
title 'Tabulate';
where region in ('Asia', 'Canada');
var sales;
class region product;
table region=' '*product=' ',
sales*(min mean max) / Box='Region Product';
run;
proc report data=sashelp.shoes nowd spanrows;
title 'Report';
where region in ('Asia','Canada');
column region product sales,(min mean max);
define region / group style(column)=Header;
define product / group style(column)=Header;
define sales / analysis;
run;
ods html close;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.