DATA Step, Macro, Functions and more

Deleting file on SFTP server

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Deleting file on SFTP server

Hi

 

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


Accepted Solutions
Solution
‎02-23-2016 03:23 AM
Super User
Posts: 7,785

Re: Deleting file on SFTP server

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,785

Re: Deleting file on SFTP server

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.

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

Re: Deleting file on SFTP server

Posted in reply to KurtBremser

Hi Kurt

 

Thanks for the answer.

 

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

Super User
Posts: 7,785

Re: Deleting file on SFTP server

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.

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

Re: Deleting file on SFTP server

Posted in reply to KurtBremser

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

Solution
‎02-23-2016 03:23 AM
Super User
Posts: 7,785

Re: Deleting file on SFTP server

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 779 views
  • 0 likes
  • 2 in conversation