topic Re: Exporting SAS Data in one Excel with multiple sheets in SAS Programming

Exporting SAS Data in one Excel with multiple sheets

iSAS — Thu, 22 Mar 2018 02:38:54 GMT

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.

Re: Exporting SAS Data in one Excel with multiple sheets

art297 — Thu, 22 Mar 2018 03:13:04 GMT

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

Re: Exporting SAS Data in one Excel with multiple sheets

iSAS — Thu, 22 Mar 2018 03:23:20 GMT

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.

Re: Exporting SAS Data in one Excel with multiple sheets

art297 — Thu, 22 Mar 2018 03:29:30 GMT

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

Re: Exporting SAS Data in one Excel with multiple sheets

Reeza — Thu, 22 Mar 2018 03:35:28 GMT

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;

Re: Exporting SAS Data in one Excel with multiple sheets

iSAS — Thu, 22 Mar 2018 03:57:20 GMT

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

Re: Exporting SAS Data in one Excel with multiple sheets

iSAS — Thu, 22 Mar 2018 05:04:01 GMT

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

Re: Exporting SAS Data in one Excel with multiple sheets

Tom — Thu, 22 Mar 2018 05:05:12 GMT

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;