sas ODS - split data up on a single sheet in Excel

Question

I have a dataset that is very similar to that below:

I have no problem writing the SAS macro to split the dataset into the various departments, but I am looking for a way to split the department up within the degree level but putting both of them on the same Excel sheet, plus giving it a nice format. Sort of like this:

I'm struggling with ODS to figure out how to do anything remotely nice within SAS/Excel. Any suggestions.

Answer 1

Perhaps you could use ODS tagsets:

proc sort data=sashelp.class out=class;
   by sex;
run;


ods tagsets.excelxp file="bylines.xls" style=statistical
    options( suppress_bylines='yes' sheet_interval='none' );

ods tagsets.excelxp options( suppress_bylines='no' sheet_interval='none' );

proc print data=class;
   by sex;
run;

ods tagsets.excelxp close;

Plenty of documentation on it on the SAS website. This page is a good place to start:

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

As with other ODS destinations the output appearance is also customizable by defining/modifying existing style templates or creating your own.

ODS EXCEL (with 9.4 TS1M1+) works the same way, and produces native XLSX files:

proc sort data=sashelp.class out=class;
by sex;
run;

ods excel file="c:\temp\test.xlsx" options(sheet_interval='none'
                                           suppress_bylines='true');

proc print data=work.class;
  by sex;
run;

ods excel close;