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
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.
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.
Hi Kurt
Thanks for the answer.
How would you formulate the delete statement using the X statement syntax?
This depends on the SFTP client software you are using.
For WinSCP, see
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.
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: