Close table in another session

[Jpep]

I have scheduled code that makes the same table each night, which is shared. Some people accidentally leave the table open - then it does not update.

Is there a way in the code to close the table in someone else's session?

Thanks!

[Kurt_Bremser]

No.

But you can use operating system commands to delete the file. At least in UNIX it is possible to remove a directory entry although the file is open.

[Jpep]

That would work since I am replacing the table anyway. We have tried proc delete commands and it does not work since it is someone else's session.
What operating system commands should we attempt?
Thank you!

[Kurt_Bremser]

---

@Jpep wrote:
That would work since I am replacing the table anyway. We have tried proc delete commands and it does not work since it is someone else's session.
What operating system commands should we attempt?
Thank you!

---

This depends on the operating system that SAS is installed on. If it is UNIX, I can provide a macro that physically deletes a dataset no matter what.

[Jpep]

We have Windows on Intel 64-bit OS.

[SASKiwi]

The issue of sharing/opening SAS datasets while trying to also update them has been the subject of innumerable discussions over the years...

The bottom line is there is no easy answer but here are a few ways to deal with this:

• Move the table to a DBMS designed for multi-user updates
• Implement SAS/SHARE in your SAS environment  - requires a new SAS licence
• If all SAS users access the table via SAS metadata-controlled SAS sessions then stop and re-start your SAS metadata server each night before running your update job - this will kill all the processes locking the table
• Look at using OS commands and tools to identify who is locking the table and kill the offending SAS process
• Consider creating a SAS view of your table and only allow other users to access the view not the original table 

[LinusH]

@SASKiwi: Just want to comment about SHARE, it allows for row level locking between users. Replacing a table requires a member lock. So I don't think that it will solve this particular problem.

[SASKiwi]

Thanks @LinusH. I've never used SAS/SHARE but I think depending on the update method being used this could work judging by what I see in this link (assuming of course that all users access the multi-user table via a SAS/SHARE server):

http://support.sas.com/documentation/cdl/en/shrref/64803/HTML/default/viewer.htm#p0iqymuxvfhhnjn1u7m...

[LinusH]

@SASKiwi: as the information on that pages indicates that data statement without modify requires a member lock. The same goes for SQL create table statement.
SHARE would help if the OP rewrites the logic to update the table in place instead. Probably this is more complicated an and awkward if the end goal is to replace all observations.