Importing Multiple FTP Files

[Tom]

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;

[dwish]

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.

[dwish · Accepted solution]

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;