Issues downloading file from FTP using SAS VIYA 2023.03

[smm662002]

Hello,

SAS VIYA Version LTS  2023.03

I am trying to automate a file download from the FTP but none of the methods I tried worked. The file I am trying to download is csv format and about 4Mb. In all the SAS tests, the connection to the server is successful (so, user and password are correct) and I also connected to that server using WinSCP and downloaded the file manually. Unfortunately, I let the SAS codes running more than 10min and because no results I stopped them manually. The 3 SAS codes I tried are below. I also added the SAS logs (the the logs are almost the same). I linked some of the SAS notes to http://support.sas.com/kb/57/165.html 

Thank you for your help.

SAS Code 1:

filename test99 ftp 'file.csv' cd='/csv/' host='myhost' user='myuser' pass='pass_example' debug;

data work.test;
infile test99 dsd dlm='$' firstobs=2;
length var1-var58 $100;
input var1-var58;
run;

Code 1 Log:

NOTE: 220-FileZilla Server 1.4.1
NOTE: <<< 220-FileZilla Server 1.4.1
NOTE: <<< 220-Please visit https://filezilla-project.org/
NOTE: <<< 220 welcome to myhost new
NOTE: >>> USER myuser
NOTE: <<< 331 Please, specify the password.
NOTE: >>> PASS XXXXXXXX
NOTE: <<< 230 Login successful.
NOTE: >>> OPTS UTF8 ON
NOTE: <<< 202 UTF8 mode is always enabled. No need to send this command
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 Option not understood
NOTE: >>> OPTS UTF-8 NLST
NOTE: <<< 501 Option not understood
NOTE: >>> PORT 172,27,4,141,201,245
NOTE: <<< 200 PORT command successful.
NOTE: >>> TYPE A
NOTE: <<< 200 Type set to A
NOTE: >>> CWD /csv/
NOTE: <<< 250 CWD command successful
NOTE: >>> PWD
NOTE: <<< 257 "/csv" is current directory.
NOTE: >>> RETR file.csv
NOTE: <<< 150 Starting data transfer.
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step due to attention.
WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 0 observations and 58 variables.
WARNING: Data set WORK.TEST was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 12:42.47
cpu time 1.21 seconds

=================================================================================================

SAS Code 2

filename test99 ftp 'file.csv' cd='/csv/' host='myhost' user='myuser' pass='pass_example' debug;

proc import datafile=test99 dbms=csv out=work.test_ftp replace;
run;

Code 2 Log

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
NOTE: 220-FileZilla Server 1.4.1
NOTE: <<< 220-FileZilla Server 1.4.1
NOTE: <<< 220-Please visit https://filezilla-project.org/
NOTE: <<< 220 welcome to myhost new
NOTE: >>> USER myuser
NOTE: <<< 331 Please, specify the password.
NOTE: >>> PASS XXXXXXXX
NOTE: <<< 230 Login successful.
NOTE: >>> OPTS UTF8 ON
NOTE: <<< 202 UTF8 mode is always enabled. No need to send this command
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 Option not understood
NOTE: >>> OPTS UTF-8 NLST
NOTE: <<< 501 Option not understood
NOTE: >>> PORT 172,27,4,141,202,95
NOTE: <<< 200 PORT command successful.
NOTE: >>> TYPE A
NOTE: <<< 200 Type set to A
NOTE: >>> CWD /csv/
NOTE: <<< 250 CWD command successful
NOTE: >>> PWD
NOTE: <<< 257 "/csv" is current directory.
NOTE: >>> RETR file.csv
NOTE: <<< 150 Starting data transfer.
NOTE: The SAS System stopped processing this step due to attention.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 18:58.45
cpu time 1.71 seconds

==================================================================================================

SAS Code 3:

filename test99 ftp 'file.csv' cd='/csv/' host='myhost' user='myuser' pass='pass_example' debug;

FILENAME b_nfs "/folder1/folder2/home/folder3/b.csv";

data _null_;
infile test99;
file b_nfs lrecl=32767;
input;
put _INFILE_;
stop;
run;

NOTE: 220-FileZilla Server 1.4.1
NOTE: <<< 220-FileZilla Server 1.4.1
NOTE: <<< 220-Please visit https://filezilla-project.org/
NOTE: <<< 220 welcome to myhost new
NOTE: >>> USER myuser
NOTE: <<< 331 Please, specify the password.
NOTE: >>> PASS XXXXXXXX
NOTE: <<< 230 Login successful.
NOTE: >>> OPTS UTF8 ON
NOTE: <<< 202 UTF8 mode is always enabled. No need to send this command
NOTE: >>> OPTS UTF8 NLST
NOTE: <<< 501 Option not understood
NOTE: >>> OPTS UTF-8 NLST
NOTE: <<< 501 Option not understood
NOTE: >>> PORT 172,27,4,141,144,55
NOTE: <<< 200 PORT command successful.
NOTE: >>> TYPE A
NOTE: <<< 200 Type set to A
NOTE: >>> CWD /csv/
NOTE: <<< 250 CWD command successful
NOTE: >>> PWD
NOTE: <<< 257 "/csv" is current directory.
NOTE: >>> RETR file.csv
NOTE: <<< 150 Starting data transfer.
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step due to attention.
NOTE: DATA statement used (Total process time):
real time 10:31.49
cpu time 1.02 seconds  

[Tom]

The last test is the only one you need to prove if the CSV file can be transfered.

What is the SIZE of the file you are trying to transfer?

If it is large what happens if you try transferring a small file from the same FTP server?

[smm662002]

Hi,

Thank you for your answer. 

The file I try to download is about 4Mb.

Regards,

smm662002

[gwootton · Accepted solution]

I suspect the issue here is that SAS is telling the FTP server to connect to its pod's internal IP address and port (the "PORT" command in your output) to send the data to the client, which is not reachable by the FTP server. Try using the passive FTP option in your filename statement.

[smm662002]

Hi,

Thank you for your answer.

It works.

Thank you so much.

"Passive" filename ftp option solved the issue.

Thank you,

smm662002