SAS - Multiple Proc Reports on one excel worksheet

6.3k views Asked by At

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.

1

There are 1 answers

0
Dmitry Shopin On BEST ANSWER

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)