Known:
There is no option to place multiple individual ODS outputs at specific locations in a specific worksheet.
You can tell ODS EXCEL the initial cell at which output is to be placed
ODS EXCEL OPTIONS(START_AT="<column>,<row>");
Based on these two facts you can create a work around as follows:
Send output to a workbook (*.xlsx)
Produce first tabulation in one worksheet
Produce second tabulation in second worksheet
Run a Powershell script to open the workbook and combine the two worksheets into one
Example:
ods _all_ close;
ods noresults;
filename xlsout 'my report.xlsx';
filename script 'merge sheets.ps1';
ods excel file=xlsout;
* first table in first worksheet;
proc tabulate data=sashelp.cars;
class type origin;
table type,origin/nocellmerge;
run;
ods excel options (start_at="6,1");
* second table in second worksheet.
* right of the where the first table is;
proc tabulate data=sashelp.cars;
class drivetrain origin;
table drivetrain,origin/nocellmerge;
run;
ods excel close;
* write powershell script and run it;
data _null_;
file script;
length outline $256;
input;
outline = resolve(_infile_);
put outline;
datalines4;
$xl = new-object -c excel.application
$xl.DisplayAlerts = $false # prevent 'are you sure' dialogs
$wb = $xl.Workbooks.Open("%sysfunc(pathname(xlsout))") # open SAS generated Excel
$ws1 = $wb.Worksheets(1)
$ws2 = $wb.Worksheets(2)
$row = $ws2.UsedRange.Cells(1,1).Row # determine where paste will go
$col = $ws2.UsedRange.Cells(1,1).Column
$ws2.UsedRange.Copy() # copy output in 2nd sheet to clipboard
$ws1.Cells($row,$col).PasteSpecial(-4104) # paste into 1st sheet at proper location
$ws1.Cells(1,1).Select() # deselect paste range
$ws2.Delete() # remove second sheet
$wb.Save() # save without 'are you sure' dialog
$xl.quit()
;;;;
options xwait xsync xmin;
%sysexec powershell -file "%sysfunc(pathname(ps1))";
filename script;
filename xlsout; ods results;
Final saved Excel
... View more
