The SAS Output Delivery System and reporting techniques

Merge header cells in report

Accepted Solution Solved
Reply
Super User
Posts: 19,770
Accepted Solution

Merge header cells in report

I'm using tagsets.ExcelXp to export data to excel and have multiple levels on my header line.

Is there a way using tagsets to get the Name variable to merge into the top two left cells?

The attached picture shows the output at the top and what I'd like at the bottom.

ods tagsets.excelxp file='C:\sample.xls';

proc report data=sashelp.class nowd;

column ( 'name' name) ('measurements' age sex height weight);

define name /'';

run;

ods tagsets.excelxp close;


merge cells example.jpg

Accepted Solutions
Solution
‎03-30-2012 12:28 AM
SAS Super FREQ
Posts: 8,862

Re: Merge header cells in report

Hi,

  PROC REPORT does not give you a way to merge cells the way you want. You can put the label for NAME on the same row with AGE, SEX, HEIGHT and WEIGHT labels. but with your current code, the string in your spanning header for NAME will be on the same row with the label 'Measurements'. Either way, you will have 1 row for the text 'Measurements' and 1 row for the column headers. The row created for 'Measurements" will put an empty cell above the label for NAME.

  TABULATE has a BOX area that looks the way you want, but TABULATE usually does summary reports and while you could get SEX into the report, you could not get it positioned the way you want. And, with 2 variables in the ROW dimension, you couldn't get them both in the BOX and lined up.

  Compare the 2 outputs produced by the program below. I think the REPORT example looks better than the TABULATE example and a detailed report is easier to produce with PROC REPORT.

cynthia

ods tagsets.excelxp file='C:\temp\sample.xls';

proc report data=sashelp.class nowd split='*';

column name ('measurements' age sex height weight);

define name /'Name';

run;

proc tabulate data=sashelp.class;

  class name sex;

  var age height weight;

  table name*sex,

         sum='Measurements'*(age height weight)/

         box='Box Area';

run;

ods _all_ close;

View solution in original post


All Replies
Solution
‎03-30-2012 12:28 AM
SAS Super FREQ
Posts: 8,862

Re: Merge header cells in report

Hi,

  PROC REPORT does not give you a way to merge cells the way you want. You can put the label for NAME on the same row with AGE, SEX, HEIGHT and WEIGHT labels. but with your current code, the string in your spanning header for NAME will be on the same row with the label 'Measurements'. Either way, you will have 1 row for the text 'Measurements' and 1 row for the column headers. The row created for 'Measurements" will put an empty cell above the label for NAME.

  TABULATE has a BOX area that looks the way you want, but TABULATE usually does summary reports and while you could get SEX into the report, you could not get it positioned the way you want. And, with 2 variables in the ROW dimension, you couldn't get them both in the BOX and lined up.

  Compare the 2 outputs produced by the program below. I think the REPORT example looks better than the TABULATE example and a detailed report is easier to produce with PROC REPORT.

cynthia

ods tagsets.excelxp file='C:\temp\sample.xls';

proc report data=sashelp.class nowd split='*';

column name ('measurements' age sex height weight);

define name /'Name';

run;

proc tabulate data=sashelp.class;

  class name sex;

  var age height weight;

  table name*sex,

         sum='Measurements'*(age height weight)/

         box='Box Area';

run;

ods _all_ close;

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 1782 views
  • 0 likes
  • 2 in conversation