[SOLVED] SAS - Multiple Proc Reports on one excel worksheet

SAS - Multiple Proc Reports on one excel worksheet

Is it possible to use proc report to stack multiple tables on one excel worksheet? For example, below is a simplfied version of my code where I'd like to display table1 using the first proc report, then skip a row and display table2 immediately below it on the same worksheet.

ods listing close;
ods results off;
ods tagsets.excelxp file="c:\temp\char.xls" style=esgexcel ;

proc report data=table1 missing nowindows spacing=1;
column  field1
        field2
        field3 
        ;

define field1 /   'acct';
define field2 / format=mmddyy10.  'date';
define field3 / format=dollar22.2 'value';

run;

proc report data=table2 missing nowindows spacing=1;
column  field1
        field2
        field3 
        ;

define field1 /   'acct';
define field2 / format=mmddyy10.  'date';
define field3 / format=dollar22.2 'value';

run;

ods tagsets.excelxp close;
ods listing;
ods results;

It doesn't work though. It puts both proc reports on separate worksheets.

Solution

You have to add sheet_interval=none option to ods tagsets.excelxp:

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

It will force SAS to put all subsequent tables (or bylines, or footnotes etc) to the same sheet. And if you want to change spacing between tables (which is by default 1 row) you can use option skip_space:

skip_space='x1,x2,x3,x4,x5'

where:

x1=spacing for tables (default 1)

x2=spacing for bylines (default 0)

x3=spacing for titles (default 1)

x4=spacing for footers (default 1)

x5=spacing for pageBreaks (default 1)