Importing Multiple FTP Files

[dwish]

Hi Everyone,

I'm trying to import multiple files that are on an FTP.

I'm stuck at the part where I'm having the data step that creates the tables go through the different files.  Since I'm pulling the files from an FTP the FTPREF(filename) convention doesn't work with the infile filevar = .

Any suggestions on the best way to do this?  All the files are formatted the same.

Thanks!

[art297]

Why wouldn't the method work?  You can redefine the fileref as often as needed.  It would help if you posted your attempted code.

[dwish]

I've been using this as a reference (http://support.sas.com/kb/24/712.html😞


/* Path to files to be read are in the DATALINES.               */
/* Each file is read in turn with the same INPUT statement.     */
/* The END= variable is set to 1 each time the DATA step        */
/* comes to the end of a file.                                  */
/*                                                              */
/* Read the name of the file to be read from the DATALINES and  */
/* store it in FIL2READ.  The file is then read in the DO WHILE */
/* loop.  At the end of the file, the DO loop ends, control     */
/* passes back to the top of DATA step and the process starts   */
/* over again until all files have been read.                   */
/*                                                              */
/* The argument "dummy" in the INFILE statement is a place-     */
/* holder used in place of a file reference.                    */
          
data one;
  infile datalines;

  /* Ensure fully qualified path will fit in FIL2READ */
  length fil2read $40;

  /* Input path of file to be read from DATALINES */
  input fil2read $;

  infile dummy filevar=fil2read end=done;
  do while(not done);

    /* Input statement for files to be read */
    input @1 date date9. @11 quanity item $ price totcost; 
    output;
  end;     
datalines;
c:\temp\extfile1.txt
c:\temp\extfile2.txt
c:\temp\extfile3.txt
;         
           
proc print data=one;
run;

Here is the code I've written:

/* LINK TO THE FTP */

FILENAME datain FTP DIR lrecl = 300

                CD='/xxx/Xxx/xx/xx/xx'

                HOST='hostname'

                USER='username'

                PASS='passwd' ;

/* GET THE FTP DIRECTORY LISTING */

filename dirlist ftp '' ls

               CD='/xxx/Xxx/xx/xx/xx'

                HOST='hostname'

                USER='username'

                PASS='passwd' ;

/* What files are in the directory */

data dirlist;

          infile dirlist length=reclen;

          input fname $varying200. reclen;

          if upcase(fname)=: '201109';

run;

/* PULL THE FILES IN THE DIRECTORY THAT WE ARE LOOKING FOR */

data one;

  set dirlist;

  infile dummy datain(fname) end=done;

  do while(not done);

   /* Input statement for files to be read */

              PhoneNum         : ?? BEST10.

        cust_acct_num    : ?? BEST8.

        PhoneNum2        : ?? BEST9.

        Opt_in_dte       : ?? YYMMDD10.

        status           : $CHAR6. ;  

    output;

  end;     

But using "dummy" doesn't work, because it won't take "FTPREF(filename)".  If I take out dummy, it runs but looks for "fname" on the directory instead of cycling through the file names listed in table "dirlist".

Any thoughts?

Thanks!

[Tom]

You do not seem to be filtering which files to import.  Why not change your FTP to use MGET and change your INFILE to use FILENAME instead of FILEVAR .

This way SAS should pull all of the files from the FTP directory and set the FILENAME variable to the name of the file that is currently being read.

If you want I could try to dig up some old code where I used this method to download a block of files.

[dwish]

Thanks for the response -

I am actually filtering the filename.  I'd like to be able to pull a month's worth of files at a time (there's hundreds of files in the directory).

How would i get the FILENAME variable to cycle through the file names based on what's there?

Any more code to look at would be great - I'd appreciate it.

Thanks!

[dwish]

I just tried MGET with the wildcard, but for some reason it's not treating the wildcard appropriately.

Here's the code I used:

FILENAME datain FTP '201109*.txt' lrecl = 300

                CD='/xxx/xxx'

                HOST='hostname'

                USER='username'

                PASS='passwd' mget debug;

data one;

  infile datain ENCODING="wlatin1"

                    TERMSTR=LF

        DLM='|'

        MISSOVER

                 DSD

                    FIRSTOBS=2;

    /* Input statement for files to be read */

              INPUT PhoneNum   : ?? BEST10.

        cust_acct_num    : ?? BEST8.

        PhoneNum2        : ?? BEST9.

        Opt_in_dte       : ?? YYMMDD10.

        status           : $CHAR6. ;  

run;

And this is the error:

NOTE: <<< 200 Type set to A.

NOTE: >>> NLST 201109*.txt

NOTE: <<< 550 201109*.txt: No such file or directory.

ERROR: Physical file does not exist, 201109*.txt.

NOTE: The SAS System stopped processing this step because of errors.

[Tom]

In the first program I found I was putting the wildcards in the FILENAME statement.

filename sascode ftp '*.sas' mget recfm=v
  cd="&xpath"
  user="&user"
  pass="&pass"
  host="&host"
;

[dwish]

Strange that it's treating my wildcard as part of the filename - is there a setting/option I need to toggle?  Or do I need to use a different wildcard symbol since it's a UNIX FTP?

Or does the wildcard only work with nothing preceding it, and only a .extension following it?

[art297]

The following is a rather simplified example but, hopefully, will let you see what you might be missing:

*create some test files;

data _null_;

  file "c:\art\test1.txt";

  put "1234567890";

run;

data _null_;

  file "c:\art\test2.txt";

  put "2345678901";

  put "3456789012";

run;

data _null_;

  file "c:\art\dirlist.txt";

  put "test1";

  put "test2";

run;

data dirlist;

  infile cards truncover;

  input fname $200.;

  cards;

c:\art\test1.txt

c:\art\test2.txt

run;

/* PULL THE FILES IN THE DIRECTORY THAT WE ARE LOOKING FOR */

data one;

  set dirlist;

  infile dummy filevar=fname end=done;

  do while(not done);

     /* Input statement for files to be read */

     input PhoneNum         : ?? BEST10.;

     output;

   end;    

run;

[dwish]

Art-

Thanks for the example.

It works, until I try to change things to apply to the FTP.

The INFILE won't treat "fname" as a variable and cycle through the different values of filenames (whether I enapsulate it in the FTPFILENAME() reference, or include the FTPFILENAMEREF() in the table of filenames).  It just simply looks for a file or FILENAME reference named "fname".

Any idea how I can get around that in the data step that imports the files?

[Tom]

Did you try checking the ftp server manually from the command prompt? Perhaps your FTP server responds slightly differently than you or SAS expect.

From OS command window enter the command:

ftp -i hostname

Then after giving it your credentials move to the source directory and retreive the files.

ftp> cd path
ftp> mget 201110*.txt 

[dwish]

Good idea -

I get a "Permission denied" when I try to use MGET.  Using a regular FTP client (FileZilla), I can download the files just fine though.

But looking at the error I get, it looks like SAS isn't even translating the wildcard appropriately to begin with?

[Tom]

FileZilla is probably NOT using mget. It probably retreives the directory and then retrieves the files one by one. 

SAS does not translate the *, that function is being done by the remote FTP server. Are you sure they do not have embedded spaces or other things so that they do not match the pattern that you are trying to use? Try testing your pattern by using the DIR command instead of the MGET command to see if the files you want are actually there.

[dwish]

Gotcha.. Running mget on the FTP lists the files I need (8 or so of them) that match the convention I'm using in SAS - it just says Permission denied for each one.

Using:

filename dirlist ftp '' ls CD='/xxxx/xxx' 
                HOST='hostname'
                USER='username'
                PASS='passwd';

and

data dirlist;
          infile dirlist length=reclen;
          input fname $varying200. reclen;
          if upcase(fname)=: '201109'; 
run;

Lists the files out as well, so they're there.  I'll check with the sysadmin about mget/additional access.

[Tom]

Perhaps your server requires SFTP instead of normal FTP?