DATA Step, Macro, Functions and more

Importing Multiple FTP Files

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Importing Multiple FTP Files

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!


Accepted Solutions
Solution
‎11-29-2011 04:54 PM
Occasional Contributor
Posts: 16

Re: Importing Multiple FTP Files

I figured it out. Using the FILENAME DIR ls statement, I retrieve the filenames I'm looking for in a data step with a condition. As it's pulling these file names, I call a macro that takes the current filename variable and runs it through an INFILE, after which the data is appended to a table I have created.

Pasting the code below for future reference for anyone else hoping to import multiple FTP files in a simple fashion:

/* CREATE FILE IMPORT MACRO */

OPTIONS MPRINT;

%MACRO importFTPfiles (currentfile=);

  FILENAME ftpfiles FTP "&currentfile" lrecl = 300

                 CD='/directory'

                 HOST='host'

                 USER='user'

                 PASS='pass!' ;

  DATA currentfile;

     LENGTH

         PhoneNum           8

         cust_acct_num       8;

    FORMAT

         PhoneNum         BEST10.

         cust_acct_num     BEST10.;

     INFORMAT

         PhoneNum         BEST10.

         cust_acct_num     BEST10.;

     INFILE ftpfiles

         ENCODING="wlatin1"

  TERMSTR=LF

         DLM='|'

         MISSOVER

        DSD

  FIRSTOBS=2;

     INPUT

         PhoneNum         : ?? BEST10.

         dtv_acct_num     : ?? BEST10.;

      RUN;

  PROC APPEND base=ftpimports data=currentfile;

  RUN;

%MEND importFTPfiles;

/* CREATE TABLE TO HOUSE THE LATEST INCOMING RECORDS */

DATA tcs.ftpimports;

    LENGTH

        PhoneNum           8

        cust_acct_num       8;

    FORMAT

        PhoneNum         BEST10.

        cust_acct_num     BEST10.;

    INFORMAT

        PhoneNum         BEST10.

        cust_acct_num     BEST10.;

  CALL MISSING(of _all_);

  STOP;

run;

/* GET THE FTP DIRECTORY LISTING */

filename dirlist ftp '' ls CD='/directory'

                HOST='host'

                USER='username'

                PASS='passwd';

/* Pull listing of files, filtered and run through macro */

data dirlist;

  infile dirlist length=reclen;

  input fname $varying200. reclen;

  if upcase(fname)=: 'ACCOUNT201111';

/* execute macro while sending each file name sequentially to the macro */

  call execute('%importFTPfiles(currentfile='||fname||')');

run;

View solution in original post


All Replies
PROC Star
Posts: 7,492

Importing Multiple FTP Files

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

Occasional Contributor
Posts: 16

Importing Multiple FTP Files

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!

Super User
Super User
Posts: 7,079

Re: Importing Multiple FTP Files

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.

Occasional Contributor
Posts: 16

Importing Multiple FTP Files

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!

Occasional Contributor
Posts: 16

Importing Multiple FTP Files

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.


Super User
Super User
Posts: 7,079

Re: Importing Multiple FTP Files

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"

;

Occasional Contributor
Posts: 16

Importing Multiple FTP Files

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?

PROC Star
Posts: 7,492

Importing Multiple FTP Files

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;

Occasional Contributor
Posts: 16

Importing Multiple FTP Files

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?

Super User
Super User
Posts: 7,079

Re: Importing Multiple FTP Files

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

Occasional Contributor
Posts: 16

Importing Multiple FTP Files

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?

Super User
Super User
Posts: 7,079

Re: Importing Multiple FTP Files

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.

Occasional Contributor
Posts: 16

Re: Importing Multiple FTP Files

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.

Super User
Super User
Posts: 7,079

Re: Importing Multiple FTP Files

Perhaps your server requires SFTP instead of normal FTP?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 3032 views
  • 3 likes
  • 3 in conversation