topic Re: Close table in another session in SAS Programming

Close table in another session

Jpep — Tue, 17 May 2016 17:24:29 GMT

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!

Re: Close table in another session

Kurt_Bremser — Tue, 17 May 2016 19:46:49 GMT

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.

Re: Close table in another session

SASKiwi — Tue, 17 May 2016 20:05:09 GMT

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

Re: Close table in another session

Jpep — Tue, 17 May 2016 20:35:12 GMT

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!

Re: Close table in another session

LinusH — Tue, 17 May 2016 20:39:56 GMT

@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.

Re: Close table in another session

SASKiwi — Tue, 17 May 2016 23:35:23 GMT

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#p0iqymuxvfhhnjn1u7msnsiej9mm.htm

Re: Close table in another session

LinusH — Wed, 18 May 2016 05:10:25 GMT

@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.

Re: Close table in another session

Kurt_Bremser — Wed, 18 May 2016 05:23:14 GMT

@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.

Re: Close table in another session

Jpep — Wed, 18 May 2016 13:50:14 GMT

We have Windows on Intel 64-bit OS.