Project failed due to CSV file being used/opened by other user or programme

[Suzy_Cat]

Hey there,

I just found that if someone is using the CSV file that the SAS programme is trying to override then it will failed to export to CSV on the designated location.

It is hard to tell when and who will be using it at the time as the outputs meant to be used by other programme and users.

Any solution to resolve this?

Thanks

[Norman21]

One possible solution is given at the end of this thread:

https://communities.sas.com/t5/ODS-and-Base-Reporting/ERROR-File-is-in-use/td-p/32964

[Suzy_Cat]

Very useful info suggested. Thank you for your help 🙂

[Kurt_Bremser]

If your SAS runs on a UNIX, there is a trick to prevent the collision, but it requires that XCMD (running external commands) is enabled.

[Suzy_Cat]

my SAS is running on a server.

[Patrick]

You could go for an approach where you create a timestamp as part of the file name. This way the file you create is always new (new name) and no one can lock it.

If dealing with downstream processes where you can't control dependencies (here: users opening your file) I'd always go for a design approach where you first create the file in another location which no one can access and then have a separate copy/move process to the location for downstream consumption. 

As @Kurt_Bremser  hints if your SAS Servers runs under Unix/Linux and you've got SAS option XCMD set then there are ways to copy/move your file to the final destination even if there is a lock on the target file. 

[Suzy_Cat]

Sounds like a good solution - Thank  you @Patrick , I will test if it works at my end.

Any chance to provide the code to copy/back up data to a different folder by SAS option XCMD ?

Cheers

Sue

[Patrick]

SAS option XCMD enables you to issue OS level commands out of a SAS session. What the valid commands are depends on your operating system (the OS of the SAS server).

The following command will show you in the SAS log if the option is set to XCMD or NOXCMD

proc options option=xcmd;
run;

And this macro variable will tell you the OS of your SAS Server

%put &=SYSHOSTINFOLONG;

You need to tell us what you have for us to support you further.

[Suzy_Cat]

XCMD The X Command is valid in this SAS session.
NOTE: PROCEDURE OPTIONS used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


18
19 %put &=SYSHOSTINFOLONG;
WARNING: Apparent symbolic reference SYSHOSTINFOLONG not resolved.
SYSHOSTINFOLONG

[Patrick]

19 %put &=SYSHOSTINFOLONG;
WARNING: Apparent symbolic reference SYSHOSTINFOLONG not resolved.
SYSHOSTINFOLONG

---

Then execute below and examine if any of the macro variables provides the info which OS you're having.

%put _all_;

If above doesn't return the required info then run below. This tells you which type of OS you've got a SAS license for.

proc setinit;
run;

[Patrick]

That doesn't tell us the OS. There must be a line a bit higher up in the SAS log starting with:  

Operating System:  

[Suzy_Cat]

My bad.

Operating System: LIN X64 .

[Patrick · Accepted solution]

I can't fully test it but I believe the mv command doesn't care about locks and though should allow to replace the target file even if a user locks it (this user will then still see the old data until refreshing the connection).

data _null_;
  x 'mv -f ~/test_new.txt ~/test_orig.txt';
  stop;
run;

If the mv alone doesn't work (but it should) then you would first issue a rename command for the target file and then move the new source file to the target (with the name of the target file you want to have it).

The ~ in above code is short hand for the Home directory. You would likely want to replace this with the actual paths for source and target. 

If using double quotes then you can also use SAS Macro variables in the X command (like for the path names). These macro variables get then resolved before the X command executes.

[Suzy_Cat]

testing...
hope it works 🙂