If you have a firewall that prevents active mode, and a server that doesn't support passive mode, you're hosed.
I suggest using other ftp software to see if a connection can be made manually.
Hello All,
I am keep getting the error "ERROR: Invalid Reply received for the PASV command.." for line 205 to 230, the get_filenames marco. Would someone please take a look and let me know what wrong is in there. I tried to single and double quotes in few places but no help. I executed that portion in SAS EG and got no error. I am sure why.
Thank you for your help again.
@Mixer - we are human, not computers. You have attached the source - better send that part of the log containing
the step and the error message.
Take a look at the documentation in the next link:
You can google for "sas online documentation <strings to search>".
Beyond please describe your sas installation as sas version, client/server, OS - that might help the forum to point at the root problem.
Hi Shmuel,
I have seen that document about FTP access and it is what I used to learn how to access file and folder on FTP server but thank you for the link and sorry for the lack of information here.
Attachment is the full log file which shows steps and error. The error shows at line 2156 as "ERROR: Invalid Reply received for the PASV command.."
I am using SAS EG 9.4 M4 on Windows 10.
Thank you for your help.
Next is part from the log, including the error message and the preceeding lines:
NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds WARNING: Apparent symbolic reference IMPORTFILENAME not resolved. NOTE: Variable "c" was given a default length of 32767 as the result of a function call. If you do not like this, please use a LENGTH statement to declare "c". NOTE: 220 ftp-ec2.sharefileftp.com FTP Server Ready ERROR: Invalid Reply received for the PASV command..
pay attention to the warning: Apparent symbolic reference IMPORTFILENAME not resolved.
Now I have searched in your script where is this macro variable created or used and found next lines:
proc sql noprint;
select trim(left(tranwrd("&tablename.",'_','-')||'-'||substr(put(dateinfo,8.0),1,4)||'-'||substr(put(dateinfo,8.0),5,2)||'-'||substr(put(dateinfo,8.0),7,2)))||'.csv'
into :importfilename
from Filenames_parse
where _n_ = 1 ;
quit ;
......
filename a ftp "&importfilename." cd='Ampthink/' PASSIVE
host="&sharefile_host"
user="&sharefile_ftp_user"
pass="&sharefile_ftp_pw"
recfm=v prompt;
Is _N_ a variable in the input file Filenames_parse ?
_N_ is a datastep automatic variable, unknown by SQL .
You may need convert that sql into a datastep. Try use obs=1, as in:
proc sql noprint;
select trim(left(tranwrd("&tablename.",'_','-')||'-'||substr(put(dateinfo,8.0),1,4)||'-'||substr(put(dateinfo,8.0),5,2)||'-'||substr(put(dateinfo,8.0),7,2)))||'.csv'
into :importfilename
from Filenames_parse(obs=1)
;
quit ;
We already mentioned the problem with &importfilename and the use of _n_ last Wednesday. I'm quite surprised it's still there.
@Mixer: fix that first
There are two places that utilize this _n_ datastep variable in proc sql, so I guess I need to convert these two proc sqls into datasteps. The first one is from line 510 to 515 as you mentioned in earlier post and the other one would be from line 595 to 601.
I have the datastep for the first one as below
data _null_;
set Filenames_parse (obs=1);
string = trim(left(tranwrd("&tablename.",'_','-')||'-'||substr(put(dateinfo,8.0),1,4)||'-'||substr(put(dateinfo,8.0),5,2)||'-'||su
bstr(put(dateinfo,8.0),7,2)))||'.csv';
call symput('importfilename',string);
run;
and the second one is like this
data _null_;
set Filenames_parse (_n_ = &n);
string = trim(left(tranwrd("&tablename.",'_','-')||'-'||substr(put(dateinfo,8.0),1,4)||'-'||substr(put(dateinfo,8.0),5,2)||'-'||su
bstr(put(dateinfo,8.0),7,2)))||'.csv';
call symput('importfilename',string);
run;
I ran the new script and still got the same error. As I mentioned I am very new to SAS so please bear with me.
Thank you for your help.
First of all, I see at least one place where the number of opening brackets does not match that of closing brackets.
And I'd look for a place where either a blank or a non-displayable character has gotten into a substr function (su*bstr), because that's what SAS complains about.
First thing to do, when a program fails, is to check the log:
NOTE: Line generated by the invoked macro "BUILD_TABLE". 1820 data _null_; set Filenames_parse (obs=1); string = 1820 ! trim(left(tranwrd("&tablename.",'_','-')||'-'||substr(put(dateinfo,8.0),1,4)||'-'||substr(put(dateinfo,8.0),5,2)||'-'||su 1820 ! bstr(put(dateinfo,8.0),7,2)))||'.csv'; call symput('importfilename',string); ____ 22 68 ____ 72 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, LE, LT, MAX, MIN, NE, NG, NL, OR, [, ^=, {, |, ||, ~=. ERROR 68-185: The function BSTR is unknown, or cannot be accessed. ERROR 72-185: The LEFT function call has too many arguments. NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 1820:173 1820:176 NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time):
there errors. I have attached only one step from the log. Check are there more.
When you have a long statement break it into several lines like in:
string = trim(left(tranwrd("&tablename.",'_','-') || '-' ||
substr(put(dateinfo,8.0),1,4) || '-' ||
substr(put(dateinfo,8.0),5,2) || '-' ||
substr(put(dateinfo,8.0),7,2))) || '.csv';
call symput('importfilename',string);
which can be written more efficiently using catx function:
string = catx('-' , tranwrd("&tablename.",'_','-') , substr(put(dateinfo,8.0),1,4) , substr(put(dateinfo,8.0),5,2) , substr(put(dateinfo,8.0),7,2) , '.csv' );
call symput('importfilename',string);
You have a line break from line 524 to 525 in your code that splits the substr into su<BR>bstr
That causes &importfilename to not be set.
As you can see, the multiple indirection because of the macros makes code very hard to debug. Before wrapping code into macros, always make sure it works, and then don't touch it; or touch it in only one place before making a test run, so you know where to look when things go wrong.
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: