DATA Step, Macro, Functions and more

Macro to Cycle Through Filenames

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Macro to Cycle Through Filenames

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.


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

Re: Macro to Cycle Through Filenames

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


All Replies
PROC Star
Posts: 7,363

Macro to Cycle Through Filenames

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

Occasional Contributor
Posts: 16

Macro to Cycle Through Filenames

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;

Super User
Super User
Posts: 6,502

Re: Macro to Cycle Through Filenames

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;

Super User
Super User
Posts: 6,502

Macro to Cycle Through Filenames

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

Solution
‎11-29-2011 04:51 PM
Occasional Contributor
Posts: 16

Re: Macro to Cycle Through Filenames

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 672 views
  • 0 likes
  • 3 in conversation