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

kaouter — Sun, 05 Apr 2020 20:27:38 GMT

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

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

PaigeMiller — Sun, 05 Apr 2020 20:45:20 GMT

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

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

RichardDeVen — Wed, 15 Apr 2020 17:52:23 GMT

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

topic Re: How can I display two proc tabulate next to each other in a excel file+ods in ODS and Base Reporting

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

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

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