Check for Lock on an xlsx input file

[Krodde]

Hi,

Is there a way SAS can verify wether an xlsx input file is locked?

There's no way around the use of an xlsx input file.  (end user wants xlsx)

I'm building a program which would create an xlsx file, end user can then add notes to this file.

The following week a new file will be created, but if any notes were added to any of the records I'll need to add them to the new file. 

Whenever a user would lock the file I'd like the program to send an email to the user.

Thanks,

[Patrick]

I guess you could use the datastep libname function to check if an Excel file is available (or better use fopen(ref-to-excel-file) as proposed by @andreas_lds) . I believe to check which process is locking a file would require OS level commands (command depending on OS of course) and then figure out who owns the process, and then also figure out the email address of the process owner - which might or might not be available (some sites also allow to use the userid for sending email).

As an alternative: You could implement a copy process (which also should work if the file is locked) and then read from the copy which will never be locked.

[ChrisNZ]

I would use the rename function.

If the file can be renamed, it is not locked.

[Patrick]

---

@ChrisNZ wrote:

I would use the rename function.

If the file can be renamed, it is not locked.

---

@ChrisNZ  Interesting idea. Can you please elaborate?

OS level rename or a Proc Datasets rename? If OS level: What would be the end user experience if you rename the file while someone is accessing it (I believe a Linux mv allows for this; not sure about Windows)?

[Kurt_Bremser]

---

@Patrick wrote:

---

@ChrisNZ wrote:

I would use the rename function.

If the file can be renamed, it is not locked.

---

@ChrisNZ  Interesting idea. Can you please elaborate?

OS level rename or a Proc Datasets rename? If OS level: What would be the end user experience if you rename the file while someone is accessing it (I believe a Linux mv allows for this; not sure about Windows)?

---

In Windows, you can't rename when a file is in use because the file handle goes through the directory entry. In UNIX, you can rename because the directory entry is just a pointer to the inode, and the file handle goes through that. Ken Thompson got that right in 1969, MS never.

So in UNIX you need another way of detecting if a file is open or locked. Either lsof or fuser should do it.

My preferred strategy for dealing with possible locks in UNIX is to create the new version of the file parallel to the existing one (eg concatenate datasets in WORK), then remove the source, and move the new version to the permanent location; sometimes this is done in the final step like this:

data temp;
set
  prod.indataset
  update (in=new)
;
_new = new;
run;

proc sort data=temp;
by id _new;
run;

%delete_physical(prod.indataset) /* this is a macro that does the rm -f of the .sas7bdat file */

data prod.indataset;
set temp;
by id;
if last.id;
drop _new;
run;

id stands for any sorting criteria that should be unique.

The macro checks if syscc is zero; if it is not, the file is not removed, and the usual SAS safeguards (noreplace) prevent accidental overwriting with bogus data if the job encountered a problem farther up in the code.

[ChrisNZ]

>.OS level rename or a Proc Datasets rename? If OS level: What would be the end user experience if you rename the file while someone is accessing it (I believe a Linux mv allows for this; not sure about Windows)?

The rename function, not at the OS level.

I assumed this was Windows since we have an Excel file. As @Kurt_Bremser points out, Unix doesn't care much about file names even when a file is in use. The name is just an attribute.

Under windows a file being used prevents the renaming from taking place.

If it does take place, then all the better, you can do whatever you want with it, users won't find it at its usual location so are less likely to disturb your process.

You can restore the name with whatever version you want when you are done.

[andreas_lds · Accepted solution]

Another way: the function fopen(ref-to-excel-file) returns 0 if the file is in use.