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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: