topic Re: How to add multiple tables next to each other in a single excel sheet? in ODS and Base Reporting

How to add multiple tables next to each other in a single excel sheet?

Banana19 — Mon, 29 Aug 2022 18:41:22 GMT

Hi All,

As my subject line says, I was wondering whether it is possible to have multiple tables next to each other in a single excel spreadsheet. I tried ODS Excel option 'START_AT= 1,1', 'START_AT= 1,6', 'START_AT= 1,12' for each table I'm using but it is only taking the first START_AT option and displaying the rest of the tables under the first table in vertical manner.

Current Output:-

Expected Output:-

It would be great if you could help me with this.

Thanks,

Re: How to add multiple tables next to each other in a single excel sheet?

Ksharp — Tue, 30 Aug 2022 11:40:37 GMT

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

Re: How to add multiple tables next to each other in a single excel sheet?

JosvanderVelden — Tue, 30 Aug 2022 11:45:14 GMT

Have a look at these pages:
https://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets-from-sas/
https://support.sas.com/kb/57/766.html
https://blogs.sas.com/content/sgf/2019/04/23/ods-excel-control/

Re: How to add multiple tables next to each other in a single excel sheet?

ghosh — Tue, 30 Aug 2022 18:46:33 GMT

One possible solution:

you could use ODS Layout Gridded and specify an html output that can be opened into excel.

filename xlout '~/test/gridded.html';
ods html file=xlout options(pagebreak='no');

ods layout gridded columns=3 column_gutter=1in;

ods region style={background=lightblue};
title 'This is the REGION 1 TITLE';
footnote 'This is the REGION 1 FOOTNOTE';
proc print data=sashelp.class(obs=10);
run;

ods region style={background=white};
/* insert space between two tables */

ods region style={background=lightgreen};
title 'This is the REGION 2 TITLE';
footnote 'This is the REGION 2 FOOTNOTE';
proc print data=sashelp.class(firstobs=11 obs=21);
run;

ods layout end;
ods html close;

Re: How to add multiple tables next to each other in a single excel sheet?

Reeza — Tue, 30 Aug 2022 18:57:49 GMT

Unfortunately no option to natively do this within ODS Excel at the moment 😞

Re: How to add multiple tables next to each other in a single excel sheet?

Banana19 — Mon, 19 Sep 2022 19:35:41 GMT

Thanks for the reply! For some reason my users requested the report should only be in excel. So they changed the requirements. 🙂

Re: How to add multiple tables next to each other in a single excel sheet?

Reeza — Mon, 19 Sep 2022 19:37:24 GMT

If you have Adobe Pro and save it to PDF and then save to Excel from Adobe you'll have the desired format but a bit roundabout for sure.

Re: How to add multiple tables next to each other in a single excel sheet?

Banana19 — Mon, 19 Sep 2022 19:42:10 GMT

Oh okay. But I do not have Adobe PRO and the report will be exported to the users server. So once the code is kicked off, I cannot intervene. Thank you!