1

I have a dataset that is very similar to that below:
enter image description here

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:

enter image description here

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

Joe
  • 62,789
  • 6
  • 49
  • 67
user1624577
  • 547
  • 2
  • 6
  • 15

1 Answers1

2

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;
Joe
  • 62,789
  • 6
  • 49
  • 67
Robert Penridge
  • 8,424
  • 2
  • 34
  • 55
  • 1
    If you have 9.4 TS1M1, you can do the same with `ODS EXCEL`, which is the new tagset that actually creates a native format XLSX file, with most of the common options. Kevin Smith has some papers out about it (one of the developers). Rob, if you don't mind I'll edit in the code to do the same for `ODS EXCEL` (it's almost identical). – Joe Jul 17 '14 at 21:40
  • Go nuts Joe! I just did a little searching and posted the first thing I came across that did the trick... Always interested in alternative ways to solve problems... – Robert Penridge Jul 17 '14 at 21:55