Weird issues exporting to .xlsx

Reply
Frequent Contributor
Frequent Contributor
Posts: 83

Weird issues exporting to .xlsx

Hi all,

 

I have been encountering intermittent issues while using both Proc Export and Proc Append to write data to an excel file. The procedures both run fine, the log verifies the data has been exported successfully yet the file does not update. If I open the file I'm trying to write to and save it, then rerun my SAS program that seems to fix the issue. Does anyone have any idea what could be causing this and how to fix it? Most of the files I'm writing to are .xlsx files. Could this have something to do with it? There are other programs that write to .xls file that havent had this issue.

 

Appreciate any insight. Thanks. 

Super User
Posts: 11,105

Re: Weird issues exporting to .xlsx

If you show the code it might help.

Also if using the ODS EXCEL destination, I believe that is still somewhat experimental and you could be hitting the limits of the destination. I would let SAS tech support know it that case as it may help them improve the approach.

 

The fix you describe makes it sound like a file is "locked" somewhat. Make sure that the ods destination is closed if using that. And REPLACE in proc export.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Weird issues exporting to .xlsx


ballardw wrote:

If you show the code it might help.

Also if using the ODS EXCEL destination, I believe that is still somewhat experimental and you could be hitting the limits of the destination. I would let SAS tech support know it that case as it may help them improve the approach.

 

The fix you describe makes it sound like a file is "locked" somewhat. Make sure that the ods destination is closed if using that. And REPLACE in proc export.


The code is fairly straight forward. 

 

Proc Export:

proc export
data = Formats
outfile = "&directory"
DBMS = EXCEL replace;
SHEET = "formats";
RUN;

Proc Append:

Libname WrkBk EXCEL "&directory" Scan_Text = No;
ods listing;
PROC DATASETS 
LIB = WrkBk;
DELETE Report;
RUN;
QUIT;
PROC APPEND 
BASE = WrkBk.Report 
DATA = Report Force;
RUN;
ods listing close;
Libname WrkBk CLEAR;

The data I'm export is really small, too. Maybe 6000 rows of data, if that, so I know I'm not hitting data limits with excel.

 

The solution I've been using does seem like the file is locked but it isn't, or at least doesn't behave like a locked file. I can open it; other people can open, and no one gets a locked workbook message. 

 

I've noticed 2 things though. First, the files I'm writing to are xlsx and not xls. There are many other programs with virtually the same code as listed above the write to xls without any issue. I'm wondering if that has something to do with it. I think I'm ging to try converting the files to .xls and letting the programs run to see if that solves the problem. 

 

Second, I've noticed that when I'm exporting to excel and creating a brand new sheet, previous sheets will stay selected when I open the workbook. I don't think this has anything to do with it but its something odd I've noticed. 

 

Super User
Posts: 3,233

Re: Weird issues exporting to .xlsx

I've had some issues with the new XLSX LIBNAME engine available in SAS 9.4M2. As a consequence I went back to using the EXCEL engine. Which engine are you using for APPENDing?  

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Weird issues exporting to .xlsx

[ Edited ]

SASKiwi wrote:

I've had some issues with the new XLSX LIBNAME engine available in SAS 9.4M2. As a consequence I went back to using the EXCEL engine. Which engine are you using for APPENDing?  


I'm using the EXCEL libname engine and trying to write to an .xlsx file. I bet that's the problem. We're using SAS 9.4 so I think before converting my files back to .xls I'll try changing the libname engine to XLSX.

 

Thanks for the info!

Ask a Question
Discussion stats
  • 4 replies
  • 326 views
  • 0 likes
  • 3 in conversation