How can I display two proc tabulate next to each other in a excel file+ods

[kaouter]

Hello, I've created an excel file using ods.

The file contains few of proc tabulate, each one is displayed after the other one .( see screenshot)

I want to display two proc tabulate next to each other , how can I do that?

thank you for your help

[PaigeMiller]

As far as I know, ODS EXCEL does not have the capability of displaying tables next to one another.

[RichardDeVen]

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