DATA Step, Macro, Functions and more

Close table in another session

Reply
Occasional Contributor
Posts: 5

Close table in another session

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!

Super User
Posts: 7,868

Re: Close table in another session

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Close table in another session

Posted in reply to KurtBremser
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!
Super User
Posts: 7,868

Re: Close table in another session


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Close table in another session

Posted in reply to KurtBremser
We have Windows on Intel 64-bit OS.
Super User
Posts: 3,261

Re: Close table in another session

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 
Super User
Posts: 5,441

Re: Close table in another session

@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.
Data never sleeps
Super User
Posts: 3,261

Re: Close table in another session

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

 

 

 

Super User
Posts: 5,441

Re: Close table in another session

@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.
Data never sleeps
Ask a Question
Discussion stats
  • 8 replies
  • 436 views
  • 7 likes
  • 4 in conversation