Hello all, in a previous question I needed help with a wire format import see: https://communities.sas.com/t5/SAS-Programming/fedwire-funds-format-files-with-tags-and-elements/td-p/762167
This has been working fantastically, however, as the number of files grow, I would like to use a dataset that list the files in a specific directory, compare that to my data already processed and only process new files. I've been able to do this for other imported text and csv files. With this particular layout of data, I'm only able to get the first record to import and it doesn't import the other two records. I tried adding a end=done, and do while (not done) but it runs indefinitely, tried putting the do while in different locations, but I'm at a lost.
%macro wires(path);
filename pipedir pipe " dir ""&path\"" " lrecl=5000;
data FILES_LISTED;
infile pipedir truncover;
input line $char1000.;
length DIRECTORY $1000;
retain DIRECTORY;
if line =' ' or index(upcase(line),'<DIR>') or left(upcase(line)) = :'VOLUME' then delete;
if left(upcase(line)) = :'DIRECTORY OF' then DIRECTORY = left(substr(line,index(upcase(line),'DIRECTORY OF')+12));
if left(upcase(line)) = :'DIRECTORY OF' then delete;
if input(substr(line,1,10),?? mmddyy10.) = . then delete;
FILE_DATE = input(substr(line,1,20),?? anydtdtm32.);
format FILE_DATE NLDATMAP20.;
FILE_SIZE = INPUT(substr(line,21,19),comma20.);
FILE_NAME = STRIP(substr(line,40));
FILES = CATX("\",DIRECTORY,FILE_NAME);
drop line;
if index(upcase(FILE_NAME),'.ACK') or index(upcase(FILE_NAME),'.DAT') or index(upcase(FILE_NAME),'.XLSX')
or index(upcase(FILE_NAME),'.XLS') or index(upcase(FILE_NAME),'.PDF') or index(upcase(FILE_NAME),'.LNK')
or index(upcase(FILE_NAME),'.ZIP') or index(upcase(FILE_NAME),'.RPT') or index(upcase(FILE_NAME),'.CSV')
or index(upcase(FILE_NAME),'.PRC643') or index(upcase(FILE_NAME),'.TSV') then delete;
run;
PROC SQL;
CREATE TABLE WORK.NEW_FILES AS SELECT
t1.DIRECTORY,
t1.FILE_DATE,
t1.FILE_SIZE,
t1.FILE_NAME,
t1.FILES
FROM WORK.FILES_LISTED t1
WHERE FILE_NAME LIKE "%.txt";
QUIT;
data dsn;
length row col tag 8 extra $32 text $1000;
set NEW_FILES;
location=cats("&path\",FILE_NAME);
infile dummy filevar=location DLM="{}" truncover end=done;
do while (not done);
row+1;
input tag ?? @1 @;
if missing(tag) then input extra @ ;
do col=1 by 1 until(tag=' ');
input tag text @;
if tag ne ' ' then output;
end;
end;
run;
%mend wires;
%wires(C:\Users\jwalker\Desktop\DataFileInputs\Sablewires\New Folder)
... View more
