BookmarkSubscribeRSS Feed
iSAS
Quartz | Level 8

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.

7 REPLIES 7
art297
Opal | Level 21

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
Quartz | Level 8
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
Opal | Level 21

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
Quartz | Level 8
The sheet was already specified but the error is still the same
Tom
Super User Tom
Super User

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
Super User

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
Quartz | Level 8
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 604 views
  • 0 likes
  • 4 in conversation