BookmarkSubscribeRSS Feed
Kurt_Bremser
Super User

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.

Mixer
Obsidian | Level 7

@Kurt_Bremser@Tom@Shmuel

 

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.

Shmuel
Garnet | Level 18

@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:

http://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=lestmtsglobal&docsetTarget...

 

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.

Mixer
Obsidian | Level 7

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.

 

Shmuel
Garnet | Level 18

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 ;
Mixer
Obsidian | Level 7

@Kurt_Bremser @Shmuel

 

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.

 

Kurt_Bremser
Super User

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.

Shmuel
Garnet | Level 18

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);
Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 24 replies
  • 3622 views
  • 0 likes
  • 4 in conversation