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.
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 "¤tfile" 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;
Can you post a couple of lines from the file that you get and are trying to import?
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;
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;
Actually you can use the FILEVAR option on the infile statement to loop through the files.
Look at this post from last month.
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 "¤tfile" 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.