Rerun and update the SAS dataset when another user has it open

[Santoshrk]

Hi, 

I would like to rerun and update the dataset 'eg' when another user has it open.
To achieve this task, I have created eg.SAS7BDAT dataset in the library called 'test' and set the password to the dataset with the proc datasets option as below.
But, when I'm trying to refresh the dataset (when another user has it open), it is throwing lock is not available message as below. 
ERROR: A lock is not available for TEST.EG.DATA.

is there any way to overcome this issue? Any help is much appreciated. (Sample code that I'm trying is below) 

proc datasets memtype = data library = test nolist;
modify eg (write = dummy/ alter = dummy/); *Removes the password dummy;
run; quit;

data test.eg; set final; run;

proc datasets memtype = data library = test pw = vrtx nolist;
modify eg (write = dummy alter = dummy); *Set the password dummy;
run; quit;

[SASKiwi]

What OS do your SAS sessions run on? @Kurt_Bremser has a backdoor solution for Unix SAS but not for other OSs. SAS has a product for this type of scenario - SAS/SHARE but I don't imagine you would want to get another SAS product just for this 😉

[Santoshrk]

I'm using Windows SAS. Need solution on this same OS. 🙂

[Kurt_Bremser]

You need to get all users to close the dataset. There is no workaround.

[Kurt_Bremser]

Mind that the backdoor/workaround mentioned by @SASKiwi needs a complete rewrite of the dataset. It is not able to let you make changes on a dataset. You have to remove and then recreate the dataset. And, as mentioned, it only works on UNIX systems.

[Santoshrk]

@Kurt_Bremser Thanks for sharing this info, It's interesting to know.
At my workplace, we have only Windows setup.
Is it completely impossible on the Windows evnvironment? Because we have multiple teams at different locations. So, we have been requesting the teams to close the datasets several times a day before refreshing them. I'm okay to recreate the dataset and do some workaround if there is a way in Windows Environment.

[Reeza]

Are you asking as an analyst or administrator?

[Santoshrk]

analyst

[Kurt_Bremser]

Windows does not allow (because of its simpler filesystem architecture) to remove a directory entry for an open file.

What you can do is set up a service window during which client accesses are terminated. Then you can update your datasets without locks getting in your way.

[Santoshrk]

x attrib -r "&filepath.\t-eg.rtf" ; *Removes Read Only attribute;
%dorpt; *Proc Report macro;
x attrib +r "&filepath.\t-eg.rtf" ; *Enables Read Only attribute;

With the help of X command, I'm able to update the files(.rtf) in Windows environment when they are open as ReadOnly mode at another user.

[Kurt_Bremser]

I take it that the software used to display the RTF file closes it after reading it (as it won't be able to update it anyway). That is not what SAS does when a dataset is open in a viewtable window. That keeps the dataset file opened for as long as the dataset is viewed.

[SASKiwi · Accepted solution]

@Santoshrk  - If you can ensure that the SAS dataset is only locked for short periods then the FILELOCKWAIT option can be useful. Perhaps this would work if users created a copy of the dataset or referenced a view of it that might be workable?

[Santoshrk]

Can we add any program to send an automatic notification (through the SAS system) to another user to "close" the dataset, when we are trying to refresh them from our end?
Just curious to know about this possibility, instead of sending an email to the entire team and asking to close the dataset, if we can push some message to the target user/users, that would solve the issue more efficient way. 

[Kurt_Bremser]

This would involve use of external functions.

On UNIX, I'd run the fuser command on the dataset file, parse out the process numbers, and use ps to get the system username for each process (Windows hopefully provides equivalent commands) . From that you need to look up the user's email (or other contact data) from any means available, and then send the notifications.

Surely doable, but not with 10 lines of code.

[SASKiwi]

@Santoshrk - Don't know if this is doable under Windows. You could ask that question on a Windows forum because SAS doesn't really come into it. Another brute force option would be close all SAS workspace sessions say at a particular off-peak time, say early morning, and run your dataset update then. This would only work if one daily update is all you need and other SAS users don't normally run jobs at that time.