Help using Base SAS procedures

Proc Tabulate Output

Accepted Solution Solved
Reply
Regular Contributor
Posts: 184
Accepted Solution

Proc Tabulate Output

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;


Accepted Solutions
Solution
‎08-04-2014 02:23 PM
SAS Super FREQ
Posts: 8,868

Re: Proc Tabulate Output

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.

View solution in original post


All Replies
Solution
‎08-04-2014 02:23 PM
SAS Super FREQ
Posts: 8,868

Re: Proc Tabulate Output

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.

Trusted Advisor
Posts: 1,228

Re: Proc Tabulate Output

Posted in reply to Cynthia_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;

Regular Contributor
Posts: 184

Re: Proc Tabulate Output

Posted in reply to Cynthia_sas

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!

Super User
Posts: 11,343

Re: Proc Tabulate Output

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'

SAS Super FREQ
Posts: 8,868

Re: Proc Tabulate Output

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

Regular Contributor
Posts: 184

Re: Proc Tabulate Output

Posted in reply to Cynthia_sas

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?

SAS Super FREQ
Posts: 8,868

Re: Proc Tabulate Output


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;


compare_tab_report.png
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 6210 views
  • 4 likes
  • 4 in conversation