topic arrangement of sas reports side by side on ods Excel in SAS Programming

arrangement of sas reports side by side on ods Excel

snip — Mon, 28 Aug 2023 14:32:38 GMT

I am facing an obstacle while creating a report, and I need your assistance, please. My current program generates the arrays successively. However, I would like the flexibility to arrange them according to my preferences. For example, in the first tab, I would like to place tables 1 and 2 side by side, and then the third table below those. In short, I want to be able to arrange the tables at my convenience on an Excel sheet.

Unfortunately, my attempts so far have not been successful. I tried using "ods layout", but apparently the syntax I used was not correct.

ods excel file='c:\example6.xlsx'
 
options( /*autofilter = "1-3" frozen_headers = "on" frozen_rowheaders = "1"*/ start_at='C3' sheet_interval='none' sheet_name='Cl');
proc report data=sashelp.class;
column age height weight;
define age / group;
define height / mean;
define weight / mean;
rbreak before / summarize; run;
proc report data=sashelp.class;
column sex height weight;
define sex / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
proc tabulate data=sashelp.class;
    class sex age;
    table sex,age;
run;
 
ods excel options(sheet_interval='output');
ods exclude all;
data _null_;
declare odsout obj();
run;
ods select all;
 
/*******************************************************************/
ods excel options(sheet_interval='none' sheet_name='H');
proc report data=sashelp.heart;
column bp_status height weight;
define bp_status / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
proc report data=sashelp.heart;
column sex height weight;
define sex / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
 
ods excel options(sheet_interval='output');
ods exclude all;
data _null_;
declare odsout obj();
run;
ods select all;
/*******************************************************************/
 
ods excel options(sheet_interval='none' sheet_name='ttt');
proc report data=sashelp.heart;
column bp_status height weight;
define bp_status / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
proc report data=sashelp.heart;
column sex height weight;
define sex / group;
define height / mean;
define weight / mean;
rbreak before / summarize;
run;
ods excel close;

Re: arrangement of sas reports side by side on ods Excel

PaigeMiller — Mon, 28 Aug 2023 14:46:42 GMT

ODS EXCEL does not have the capability of positioning reports (or graphics) side by side.

You could try to produce side-by-side reports all within PROC REPORT (although this may not be possible depending on what the reports are) and having PROC REPORT produce blank columns between.

Re: arrangement of sas reports side by side on ods Excel

snip — Mon, 28 Aug 2023 15:51:07 GMT

Hello Miller,
Your idea is great...
I think I can modify my data to have all of my data on the same table and add the Gap column.
Thank you very much your help is precious

ods excel file='c:\example6.xlsx' 

options( /*autofilter = "1-3" frozen_headers = "on" frozen_rowheaders = "1" */start_at='C3' sheet_interval='none' sheet_name='C5');
proc format;
    value gapf .,low-high=' ';
run;
proc report data=sashelp.class;
column age height gap weight;
define age / group; 
define height / mean; 
define weight / mean; 
define gap / ' ' style(column)={BORDERTOPCOLOR=white BORDERBOTTOMCOLOR=white background=white } ;
define gap/'' format=gapf. style(header)={background=white BORDERBOTTOMCOLOR=white BORDERTOPCOLOR=white};
rbreak before / summarize; run;

Re: arrangement of sas reports side by side on ods Excel

Ksharp — Tue, 29 Aug 2023 11:29:52 GMT

https://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html