topic Re: proc datasets delete not working when using libname with excel engine in SAS Procedures

proc datasets delete not working when using libname with excel engine

CurlerBob — Thu, 11 Sep 2014 15:42:49 GMT

I am using a libname statement to access an excel file. In the past, we have found that when writing out to an excel file using this methodology the data is not always replaced. To avoid that problem, we use a proc datasets and then delete the dataset before trying to replace it. In this particular program, I have five different datasets that I am writing out to the excel file. Three of them are cleared with the delete statement, two are not. The two that are not are large (about 96,000 and 19,000 records). The following is the code I am running:

LIBNAME WrkBk EXCEL 'network location\Output Excel File.xlsm';

PROC DATASETS LIB=WrkBk;

DELETE BigData1;

RUN;

QUIT;

Data WrkBk.BigData1;

set work.BigData1Ready;

RUN;

LIBNAME WrkBk CLEAR;

We are using Excel 2010. SAS 9.2 TS Level 2M3. After the delete statement executes, the "dataset" BigData1 no longer is in the list of datasets. However, if the excel file is accessed before the data step that populates the excel file is executed, the data is still there. The three smaller tables all clear themselves out as I expect.

Any assistance will be greatly appreciated. Currently we are manually deleting the data before we run the code.

Re: proc datasets delete not working when using libname with excel engine

jakarman — Thu, 11 Sep 2014 17:15:11 GMT

When you open that spreadsheet dataset using excel than excel copies all data into memory
Opening the same excel thereafter will read the data out of the spreadsheetdataset not form the memory in excel.

Do you want to protect that spreadsheet you will need to implement locking/enqueing on the Windows system.

Prevent any Excel user reading that file before you want to update that. Blocks the update of updating when anyone has it opened for reading.

This enqueing is not present in Windows by default.

It all works as known is behaving (works as designed). What is your problem?

Re: proc datasets delete not working when using libname with excel engine

CurlerBob — Thu, 11 Sep 2014 18:12:59 GMT

Maybe I didn't explain the problem fully enough.

I am getting two differnet behaviors from the proc datasets; delete statement. With the three smaller datasets, the data is deleted from the excel file when the statement is submitted. That is as I expect. However, when the same statements are executed on the larger set of data, the data is not deleted from the excel file. That is not as I expect.

Re: proc datasets delete not working when using libname with excel engine

Reeza — Thu, 11 Sep 2014 18:30:35 GMT

This might be a tech support question.

I typically use a proc sql drop table instead to clear out my tables and haven't had any issues.

Re: proc datasets delete not working when using libname with excel engine

jakarman — Thu, 11 Sep 2014 18:59:40 GMT

What is see:

- Storage location on network drive (Windows) this is why I am suspicious on sharing issues.

- You had problems before as sometimes the tables was not replaced (normally delete-write after rename)

- You still have problems for deleting bigger tables (excel sheetnames).

Try to eliminated possible causes.
Check whether the same failure also is occurring when that excel file is on a local drive not on a network drive.

Can you replay this all on a local machine than you can also send that as a case to TS.
Cross your fingers not getting the answer "migrate to the latest version 9.4"

Re: proc datasets delete not working when using libname with excel engine

SASKiwi — Thu, 11 Sep 2014 19:35:28 GMT

This SAS note appears similar to the problem you are getting:

http://support.sas.com/kb/43/060.html

Re: proc datasets delete not working when using libname with excel engine

jakarman — Thu, 11 Sep 2014 19:47:32 GMT

SAS/ACCESS(R) 9.3 Interface to PC Files: Reference Is also noted at that level -cautions-.

Re: proc datasets delete not working when using libname with excel engine

CurlerBob — Thu, 11 Sep 2014 19:55:08 GMT

Similar but not quite the same. We had the problem on both a table over and under 65K records. I just reran to see if the tables were still not clearing after we did the manual delete of the lines yesterday. It turns out that now the tables are being deletid with the proc datasets statement. My hypothesis is that there is a formula somewhere inside all of those rows of data and that was causing the issue. I'm still trying to verify.

In regards to Jaap, I doubt it is a sharing issue. Only one person really accesses the file and three of the five tables cleared as expected. The other problems we've had in the past have been primarily sorting issues. When accessing an excel file utilizing a libname statement and the excel engine, any existing table appears to be updated rather than replaced. This keeps the original order of the data but will replace the changing data. I suspect that the write out to the excel file is actually utilizing an update sql statement behind the scenes.

Re: proc datasets delete not working when using libname with excel engine

jakarman — Thu, 11 Sep 2014 20:16:48 GMT

The sharing caused by persons we could eliminated indeed.

The sharing by a network IO could be an other. A networked drive is classified as "local intranet zone" slightly behaving different as a local drive.

Seen this as UNC names coming back an used (intranet network) or drive letters (local).

With a java run-time I have experienced this made some difference on the jvm behavior.

An other weird error could be by "Opportunistic Locking and Read Caching on Microsoft Windows Networks"

At this moment a would blame the ACE driver. May be a formula instead of data in excel is the trigger.

The ACE driver is the SQL drive like interface for data-connections with Microsoft You can find it with almost every interface.

Re: proc datasets delete not working when using libname with excel engine

Ksharp — Fri, 12 Sep 2014 12:22:26 GMT

Try SQL.

proc sql;

drop table x.have ;

quit;