Exporting SAS Data in one Excel with multiple sheets

[iSAS]

Hello,

I'm exporting SAS data in one excel file with multiple sheets but I'm receiving an error when opening the excel. Below is the sample code I use and attached snapshot is the actual error I received in excel

 proc export data=data1
  outfile="&path./Output"
  dbms=xls replace;
 run;
 
 proc export data=data1
  outfile="&path./Output"
  dbms=xls replace;
 run;

I have also excuted "proc setinit" and this is the result:
Operating System:   SUN 64  .
Product expiration dates:
---Base SAS Software                                  31MAR2018 
---SAS/STAT                                           31MAR2018 
---SAS/GRAPH                                          31MAR2018 
---SAS/SHARE                                          31MAR2018 
---SAS/CONNECT                                        31MAR2018 
---SAS/SHARE*NET                                      31MAR2018 
---SAS Enterprise Miner                               31MAR2018 
---SAS Integration Technologies                       31MAR2018 
---SAS Enterprise Miner Server                        31MAR2018 
---SAS Enterprise Miner Client                        31MAR2018 
---SAS Data Quality Server                            31MAR2018 
---SAS Enterprise Guide                               31MAR2018 
---SAS/ACCESS Interface to DB2                        31MAR2018 
---SAS/ACCESS Interface to PC Files                   31MAR2018 
---SAS Data Quality Server - English (United States)  31MAR2018 
---DataFlux QKB English                               31MAR2018 
---DataFlux QKB Latin-1 encod                         31MAR2018 
---SAS Workspace Server for Local Access              31MAR2018 
---DataFlux Trans DB Driver                           31MAR2018 
---SAS Framework Data Server                          31MAR2018 
---SAS Add-in for Microsoft Excel                     31MAR2018 
---SAS Add-in for Microsoft Outlook                   31MAR2018 
---SAS Add-in for Microsoft PowerPoint                31MAR2018 
---SAS Add-in for Microsoft Word                      31MAR2018

Is there something wrong with my code or the issue has something to do with the SAS Installation? Hoping for a reply. Thanks.

[art297]

Just a guess (as I'm not familiar with the operating system): do you get the same error if you used DBMS=XLSX?

Otherwise, sounds like you are running 32-bit Excel and that is where the conflict is. If that's the case take a look at: http://support.sas.com/kb/45/340.html

Art, CEO, AnalystFinder.com

[iSAS]

Thanks for the reply.
I'm not getting an error when using XLSX. However, it doesn't export the data in multiple sheets but overrides the old sheet with the new one.

[art297]

Your code isn't specifying the sheet name (e.g.:

 proc export data=data1
  outfile="&path./Output"
  dbms=xlsx replace;
 run;

You'd need to run it twice, once for each sheet.

Art, CEO, AnalystFinder.com

[iSAS]

The sheet was already specified but the error is still the same

[Tom]

If you want to write to two sheets in the same file then make sure to specify the sheet name.  Also make sure to specify the engine type to use, don't force PROC EXPORT to guess what engine you want to use based on the extension on the filename.

The XLSX engine is the most portable as you can use it on Unix and Windows.

Also the XLSX format is more powerful (and portable) than the XLS format.

proc export data=sashelp.class dbms=xlsx outfile="&path\multi.xlsx" replace ;
  sheet='class';
run;
proc export data=sashelp.class dbms=xlsx outfile="&path\multi.xlsx" replace ;
  sheet='class2';
run;

[Reeza]

If you have SAS 9.4 M3+ you can use ODS EXCEL instead which is pretty straightforward. 

Some of this does depend on your SAS version, I believe the initial XLSX did not support multiple sheets but later versions do. 

proc export data=sashelp.cars
  outfile="&path./Demo1.xlsx"
  dbms=xls replace;
sheet='Cars';
 run;

proc export data=sashelp.class
  outfile="&path./Demo1.xlsx"
  dbms=xls replace;
sheet='Class';
 run;

[iSAS]

Hello Reeza. Thanks for the reply.

I tried the command but I'm still receiving an error. What we use is SAS EG 5.1. What weird about our issue is sometimes it works fine but sometimes (like what happened now) it isn't