Proc Tabulate Output

[Ujjawal]

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;

[Cynthia_sas · Accepted solution]

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.

[stat_sas]

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;

[Ujjawal]

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!

[ballardw]

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'

[Cynthia_sas]

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

[Ujjawal]

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?

[Cynthia_sas]

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;