topic Re: Project failed due to CSV file being used/opened by other user or programme in SAS Programming

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

Suzy_Cat — Mon, 23 Dec 2019 01:49:18 GMT

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

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

Norman21 — Mon, 23 Dec 2019 07:39:21 GMT

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

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

Kurt_Bremser — Mon, 23 Dec 2019 07:55:00 GMT

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.

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

Suzy_Cat — Tue, 24 Dec 2019 03:55:52 GMT

Very useful info suggested. Thank you for your help 🙂

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

Suzy_Cat — Tue, 24 Dec 2019 03:57:32 GMT

my SAS is running on a server.

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

Patrick — Tue, 24 Dec 2019 07:10:08 GMT

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.

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

Suzy_Cat — Tue, 24 Dec 2019 05:22:42 GMT

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

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

Patrick — Tue, 24 Dec 2019 05:33:56 GMT

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.

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

Suzy_Cat — Tue, 24 Dec 2019 05:39:02 GMT

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

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

Patrick — Tue, 24 Dec 2019 05:43:21 GMT

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;

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

Suzy_Cat — Tue, 24 Dec 2019 06:06:56 GMT

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

Patrick — Tue, 24 Dec 2019 05:59:58 GMT

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

Operating System:

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

Suzy_Cat — Tue, 24 Dec 2019 06:01:26 GMT

My bad.

Operating System: LIN X64 .

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

Patrick — Tue, 24 Dec 2019 06:38:47 GMT

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.

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

Suzy_Cat — Tue, 24 Dec 2019 06:39:18 GMT

testing...
hope it works 🙂

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

Kurt_Bremser — Tue, 24 Dec 2019 08:00:25 GMT

In UNIX, you can remove a file even when it's opened by a user. This is possible because the main metadata of the file (owner, group, date modified, data blocks) is kept in a so-called "inode" in a separate table (not in the directory entry as Windows does it). Removing a file in UNIX removes the directory entry (called "link"), but the file itself is only deleted once no further links exist and the last open handle on the inode is closed.

Since the system might want confirmation of the remove, you should use the -f option (for "force") in the rm command.

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

Kurt_Bremser — Tue, 24 Dec 2019 09:45:05 GMT

I do have a utility macro for this that basically looks like this:

%macro del_file(ext_file);
%if %sysfunc(fileexist(&ext_file.))
%then %do;
filename delfile pipe "rm -f &ext_file. 2>&1";
data _null_;
infile delfile;
input;
put _infile_;
run;
%end;
%mend;

The macro will do the remove only when the file exists, and record all responses from the remove command in the SAS log.

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

Patrick — Fri, 27 Dec 2019 11:18:35 GMT

@Suzy_Cat

Looks like it worked

Below an alternative way to issue the mv command.

This coding approach has the advantage that you'll get all the return messages from the command back into the SAS log (untested again because my current environment has NOXCMD set).

data _null_;
  cmd='mv -f ~/test_new.txt ~/test_orig.txt';
  infile mv pipe filevar=cmd end=eof;
  while (not eof);
    input ;
    put _infile_;
  end;
  stop;
run;

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

Tom — Fri, 27 Dec 2019 13:44:40 GMT

Note that there is no need to wrap a data step around an X command. The X statement is GLOBAL,like a TITLE statement, so it can be placed anywhere.