topic Importing Multiple FTP Files in SAS Programming

Importing Multiple FTP Files

dwish — Wed, 12 Oct 2011 18:55:27 GMT

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!

Importing Multiple FTP Files

art297 — Wed, 12 Oct 2011 19:21:32 GMT

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

Importing Multiple FTP Files

dwish — Fri, 14 Oct 2011 22:45:30 GMT

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!

Re: Importing Multiple FTP Files

Tom — Fri, 14 Oct 2011 23:38:19 GMT

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.

Importing Multiple FTP Files

dwish — Fri, 14 Oct 2011 23:51:14 GMT

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!

Importing Multiple FTP Files

dwish — Sat, 15 Oct 2011 00:13:45 GMT

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.

Re: Importing Multiple FTP Files

Tom — Sat, 15 Oct 2011 00:30:03 GMT

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"
;

Importing Multiple FTP Files

art297 — Sat, 15 Oct 2011 00:30:56 GMT

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;

Importing Multiple FTP Files

dwish — Sat, 15 Oct 2011 00:39:27 GMT

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?

Re: Importing Multiple FTP Files

Tom — Sat, 15 Oct 2011 00:40:34 GMT

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

Importing Multiple FTP Files

dwish — Sat, 15 Oct 2011 00:48:02 GMT

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?

Importing Multiple FTP Files

dwish — Sat, 15 Oct 2011 00:56:12 GMT

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?

Re: Importing Multiple FTP Files

Tom — Sat, 15 Oct 2011 01:11:42 GMT

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.

Re: Importing Multiple FTP Files

dwish — Sat, 15 Oct 2011 01:15:31 GMT

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.

Re: Importing Multiple FTP Files

Tom — Sat, 15 Oct 2011 01:28:11 GMT

Perhaps your server requires SFTP instead of normal FTP?

Re: Importing Multiple FTP Files

Tom — Sat, 15 Oct 2011 01:54:56 GMT

Sounds like a permission issue. You have read access to the directory so that you can see the file name, but you do not have read access to the actual files.

If you want to use the FILEVAR option with an FTP file I found that it does not honor the CD option when using the DIR option.

Not sure if there is another way, but you can just use the fully qualified filename in the FILEVAR variable.

filename ftpdir ftp
  cd="&dname"
  host="&host"
  user="&user"
  pass="&pass"
  ls
;
data files ;
  infile ftpdir truncover;
  input fname $200. ;
run;
filename ftp ftp recfm=v
  host="&host"
  user="&user"
  pass="&pass"
  dir
;
data _null_;
  set files ;
  where fname like 'x%.sas';
  filevar="&dname/"||fname;
  put 100*'-' / fname / 100*'-';
  infile ftp filevar=filevar end=eof ;
  do while (not eof);
    input;
    put _infile_;
  end;
  eof=0;
run;

Re: Importing Multiple FTP Files

dwish — Tue, 29 Nov 2011 21:53:48 GMT

This ended up looking for the file on the SAS server instead of the ftp. "infile ftp" with ftp referenced in a filename statement as an ftp did not work.

Re: Importing Multiple FTP Files

dwish — Tue, 29 Nov 2011 21:54:00 GMT

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;