topic Re: Proc Export-Multiple tables to same sheet in SAS Programming

Proc Export-Multiple tables to same sheet

Ronein — Tue, 19 Feb 2019 05:40:19 GMT

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;

Re: Proc Export-Multiple tables to same sheet

Kurt_Bremser — Tue, 19 Feb 2019 06:17:49 GMT

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.

Re: Proc Export-Multiple tables to same sheet

SASKiwi — Tue, 19 Feb 2019 07:09:19 GMT

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

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

Re: Proc Export-Multiple tables to same sheet

RW9 — Tue, 19 Feb 2019 08:59:57 GMT

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

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.

Re: Proc Export-Multiple tables to same sheet

Ronein — Tue, 19 Feb 2019 09:03:47 GMT

SAS enterprise Guide 7.1

Re: Proc Export-Multiple tables to same sheet

andreas_lds — Tue, 19 Feb 2019 09:06:53 GMT

@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.

Re: Proc Export-Multiple tables to same sheet

Ksharp — Tue, 19 Feb 2019 13:07:28 GMT

Try to remove option REPLACE .

Re: Proc Export-Multiple tables to same sheet

SASKiwi — Tue, 19 Feb 2019 18:57:24 GMT

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

%put I am using release: &sysvlong;