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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.