topic Re: Proc Tabulate Output in SAS Procedures

Proc Tabulate Output

Ujjawal — Mon, 04 Aug 2014 17:59:25 GMT

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;

Re: Proc Tabulate Output

Cynthia_sas — Mon, 04 Aug 2014 18:23:16 GMT

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.

Re: Proc Tabulate Output

stat_sas — Mon, 04 Aug 2014 18:28:35 GMT

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;

Re: Proc Tabulate Output

Ujjawal — Mon, 04 Aug 2014 18:48:53 GMT

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!

Re: Proc Tabulate Output

ballardw — Mon, 04 Aug 2014 19:12:47 GMT

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'

Re: Proc Tabulate Output

Cynthia_sas — Tue, 05 Aug 2014 01:17:42 GMT

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

Re: Proc Tabulate Output

Ujjawal — Tue, 05 Aug 2014 17:15:11 GMT

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?

Re: Proc Tabulate Output

Cynthia_sas — Wed, 06 Aug 2014 23:27:37 GMT

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;