topic Macro to Cycle Through Filenames in SAS Programming

Macro to Cycle Through Filenames

dwish — Tue, 22 Nov 2011 19:14:21 GMT

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.

Macro to Cycle Through Filenames

art297 — Tue, 22 Nov 2011 19:22:52 GMT

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

Re: Macro to Cycle Through Filenames

Tom — Tue, 22 Nov 2011 19:45:09 GMT

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;

Macro to Cycle Through Filenames

dwish — Tue, 22 Nov 2011 19:48:58 GMT

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;

Macro to Cycle Through Filenames

Tom — Wed, 23 Nov 2011 00:23:31 GMT

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

Re: Macro to Cycle Through Filenames

dwish — Tue, 29 Nov 2011 21:51:19 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 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;