Proc Export-Multiple tables to same sheet

[Ronein]

Hello
Can I use proc export to export multiple tables to same worksheet?
In this example I want to create 3 sheets: Cars1, Cars2, Cars3.
In sheet "Cars1" I want to have 2 tables.
The code that I run create only one table in sheet "Cars1"

***Export multiple tables with Proc Export-multiple tables to same sheet***;
Proc export data=sashelp.Cars(obs=2)  
outfile="/Path/example.xlsx"
dbms=xlsx
replace;
sheet="Cars1";
Proc export data=sashelp.Cars (obs=3)  
outfile="/Path/example.xlsx"
dbms=xlsx 
replace;
sheet="Cars1";
Proc export data=sashelp.Cars(obs=4)   
outfile="/Path/example.xlsx"
dbms=xlsx 
replace;
sheet="Cars2";
Proc export data=sashelp.Cars (obs=5)  
outfile="/Path/example.xlsx"
dbms=xlsx 
replace;
sheet="Cars3";
Run;

[Kurt_Bremser]

You can't do that with proc export, as each run creates a new file/sheet (and overwrites the previous one because that is allowed by the replace option).

You can do this with ods excel and using the sheet_interval="none" and sheet_name="somename" options.

[SASKiwi]

Apparently this functionality was added in SAS 9.4M1. What SAS version are you using?

http://support.sas.com/kb/51/580.html

[Ronein]

SAS enterprise Guide 7.1

[SASKiwi]

@Ronein - that is your Enterprise Guide version, not your SAS version. Run this and post your SAS log:

%put I am using release: &sysvlong;

[andreas_lds]

---

@SASKiwi wrote:

Apparently this functionality was added in SAS 9.4M1. What SAS version are you using?

http://support.sas.com/kb/51/580.html

---

With 9.4m1 creating multiple sheets is possible, but not exporting multiple datasets to one sheet.

[RW9]

This is already being covered here:
https://communities.sas.com/t5/SAS-Programming/ods-excel-multiple-sheets/m-p/536648/highlight/false#...

Do you want to use proc export or ods excel?  I would suggest if ods excel does what you need stick with that as it is far more flexible than export, if export can even achieve what is needed.

[Ksharp]

Try to remove option REPLACE .

[andreas_lds]

@Ksharp without replace sas complaints that the file already exists.

[Satish_Parida]

Why not append the 2 data sets using data step or proc append then export.

[Ronein]

Because maybe the data sets have different variables