topic Deleting file on SFTP server in SAS Programming

Deleting file on SFTP server

Frede_1 — Wed, 17 Feb 2016 12:13:54 GMT

I have been trying to delete a text file on a SFTP server.

I have tried the following syntax:

filename SFTP SFTP '/'

DIR FILEEXT LSA

HOST = '***.**.***.**'

USER = '**********'

WAIT_MILLISECONDS = 5000

LRECL = 2000

OPTIONS = '-P ** -i \\*****.*** -pw "*******"'

DEBUG

%let rc = %sysfunc(fdelete(SFTP));

%put &rc;

The macro variable RC returns the value 10011:

_SENODEL 10011 Records cannot be deleted from this file.

But if I use an alternative program (E.g. WINSCP) to delete files on the SFTP server I dont recieve an error. So I dont think it has anything to do with autorisations.

Alot of the proposed solutions on the internet use RCMD = "dele *******.txt", but RCMD is not a valid extension on the filename SFTP syntax so that wont work either.

I am using SAS 9.4.

Does anyone have an idea how to solve the problem?

Kind regards

Frederik

Re: Deleting file on SFTP server

Kurt_Bremser — Wed, 17 Feb 2016 12:29:32 GMT

What this

filename SFTP SFTP '/'
DIR FILEEXT LSA
HOST = '***.**.***.**'
USER = '**********'
WAIT_MILLISECONDS = 5000
LRECL = 2000
OPTIONS = '-P ** -i \\*****.*** -pw "*******"'
DEBUG
;

does is:

- open a sftp connection to the server

- retrieve a directory listing

- close the connection

- hand the output over to SAS as text file

You cannot delete this, as it is only a virtual file in a pipe.

In order to delete a remote file, you best use an external SFTP utiltity in batch mode and call it with the X statement or CALL SYSTEM from SAS.

Re: Deleting file on SFTP server

Frede_1 — Wed, 17 Feb 2016 12:38:59 GMT

Hi Kurt

Thanks for the answer.

How would you formulate the delete statement using the X statement syntax?

Re: Deleting file on SFTP server

Kurt_Bremser — Wed, 17 Feb 2016 13:03:36 GMT

This depends on the SFTP client software you are using.

For WinSCP, see

Scripting and Task Automation

So, with WinSCP, you create a script file

data _null_;
file 'C:\temp\wscpscript';
put ....;
put .....;
run;

and then do

X 'winscp /script=c:\temp\wscpscript';

or, if the operation is simple enough, you can use the /command commandline option of WinSCP to enter the whole command sequence that is needed directly in the X statement.

Re: Deleting file on SFTP server

Frede_1 — Thu, 18 Feb 2016 14:04:06 GMT

Hi Kurt

So what you are saying is that I can't with ordinary Windows or SAS functions delete a file from a SFTP server?

If i can't delete a file using SAS or Windows functions, do you know if I can move the file locally on the SFTP server, e.g. from one folder to another?

I would prefer not to use an external program like WINSCP to delete/move the file on the SFTP server.

Thanks in advance

Kind regards

Frederik

Re: Deleting file on SFTP server

Kurt_Bremser — Fri, 19 Feb 2016 06:30:56 GMT

While the FTP access method is built directly into SAS, SAS uses an external sftp client when you specify the SFTP access method in a FILENAME statement. This makes it harder for SAS to use the 3rd-party software to do advanced operations like renaming or deleting files.

Even setting up SFTP for SAS is not trivial, see here:

https://support.sas.com/techsup/technote/ts800.pdf

The fact that Windows itself does not supply a simple native SFTP client (ie a port of openssh) is just another part of the incompetence in Redmond to get anything right.

So you have to use an external client like WinSCP or PuTTY, because that's what SAS itself does.

Moving a file on a remote server could be done straight with SAS functions if SAS is installed there (using SAS/CONNECT when appropriately licensed); if SAS is not installed there, you need to get remote access to that server's commandline through some kind of terminal connection (proper terminal emulators like PuTTY can be scripted), or use an SFTP client and route your operations through that.