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

[Banana19]

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,

BD

[Ksharp]

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

[JosvanderVelden]

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

[ghosh]

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;

 

[Banana19]

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

[Reeza]

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.

[Banana19]

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!

[Reeza · Accepted solution]

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