BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dwish
Calcite | Level 5

I posted about importing multiple FTP files some time ago, and just recently had some time to get back to this issue with a more focused approach.

I'm looking to create a macro that sequentially outputs file names that are listed in a table so that the latest 4 may be imported. Can this be done?

Here is what I have so far:

/* 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' ;

/* PULL ALL THE FILES IN THE FTP FOR THE LATEST MONTH */

data dirlist;

          infile dirlist length=reclen;

          input fname $varying200. reclen;

          if upcase(fname)=: '201110';

run;

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

data one;

    infile datain(*THIS IS WHERE I'D NEED THE MACRO TO OUTPUT THE FILENAMES FROM DIRLIST*) 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;    

---

I've tried using variables in datain() but it won't use the actual value of the variable.

Thanks for the help.

1 ACCEPTED SOLUTION

Accepted Solutions
dwish
Calcite | Level 5

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

5 REPLIES 5
art297
Opal | Level 21

Can you post a couple of lines from the file that you get and are trying to import?

dwish
Calcite | Level 5

The first row is a throw-away row. Just has the date and filename. All files are the same.

1610|2011-09-03|01:01|201111030100.txt

2401111181|1901987|102314311|2011-09-03|IOOO00

2401111181|1238763|230609310|2011-09-03|IOOO00

This infile works fine for importing the file:

DATA 20110831;

    LENGTH

        PhoneNum           8

        cust_acct_num       8

        PhoneNum2          8

        Opt_in_dte         8

        status           $ 6 ;

    FORMAT

        PhoneNum         BEST10.

        cust_acct_num     BEST8.

        PhoneNum2        BEST9.

        Opt_in_dte       YYMMDD10.

        status           $CHAR6. ;

    INFORMAT

        PhoneNum         BEST10.

        cust_acct_num     BEST8.

        PhoneNum2        BEST9.

        Opt_in_dte       YYMMDD10.

        status           $CHAR6. ;

    INFILE datain(201108310100.txt)

        ENCODING="wlatin1"

  TERMSTR=LF

        DLM='|'

        MISSOVER

        DSD

  FIRSTOBS=2;

    INPUT

        PhoneNum         : ?? BEST10.

        dtv_acct_num     : ?? BEST8.

        PhoneNum2        : ?? BEST9.

        Opt_in_dte       : ?? YYMMDD10.

        status           : $CHAR6. ;

RUN;

Tom
Super User Tom
Super User

You seem to have two questions embedded here.  The simple one is how to find the "latest 4".  I will leave that for you to solve.

To use a dataset with a list of files to drive a dataset with INPUT statements.

One way is to generate four 4 filenames and for then 4 data steps to read them.

You could write a macro to do it, or you could generate the code in some other way.

For example you could write the code using a data step or three and then %include the generated code.

%let path="/xx/xxxx/xxxx";

filename code temp;

data latest4;

  set latest4 ;

  file code ;

  fileref = 'file' || put(_n_,z4.);

  fname = quote("&path/"||trim(fname));

  put 'filename ' fileref 'ftp ' fname

     / "  HOST='hostname'"

     / "  USER='username'"

     / "  PASS='passwd'"

     / ';'

  ;

run;

data _null_;

  set latest4 ;

  file code mod;

  put 'data ' fileref ';'

    / '  infile ' fileref ';'

    / '  input ........ ;'

    / 'run;'

  ;

run;

data _null_;

  set latest4 end=eof;

  file code mod;

  if _n_ = 1 then put 'data want;' / '  set' @;

  put fileref @;

  if eof then put ';' / 'run;' ;

run;

%include code;

Tom
Super User Tom
Super User

Actually you can use the FILEVAR option on the infile statement to loop through the files.

Look at this post from last month.

http://communities.sas.com/message/107479#107479

dwish
Calcite | Level 5

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

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
  • 5 replies
  • 2113 views
  • 0 likes
  • 3 in conversation