BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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;
10 REPLIES 10
Kurt_Bremser
Super User

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
PROC Star

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

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

 

Ronein
Meteorite | Level 14

SAS enterprise Guide 7.1

SASKiwi
PROC Star

@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
Jade | Level 19

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User
Try to remove option REPLACE .
andreas_lds
Jade | Level 19
@Ksharp without replace sas complaints that the file already exists.
Satish_Parida
Lapis Lazuli | Level 10
Why not append the 2 data sets using data step or proc append then export.
Ronein
Meteorite | Level 14

Because maybe the data sets have different variables    

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 9169 views
  • 2 likes
  • 7 in conversation