Infile with wildcard path vs Infile with filevar

jimbobob — Mon, 29 Nov 2021 23:12:05 GMT

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)

Re: Infile with wildcard path vs Infile with filevar

Tom — Tue, 30 Nov 2021 01:46:16 GMT

You don't appear to ever read past the first line of the file because all of your INPUT statements have a trailing @. And since you are running them all in a single iteration SAS never moves to the next line.

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;
  input;
end;

Re: Infile with wildcard path vs Infile with filevar

jimbobob — Tue, 30 Nov 2021 19:13:24 GMT

Thanks Tom, so putting a Input; appears to stop the current hold of the @ and moves on to next record, if I'm understanding right. Appreciate it.

topic Re: Infile with wildcard path vs Infile with filevar in SAS Programming

Infile with wildcard path vs Infile with filevar

Re: Infile with wildcard path vs Infile with filevar

Re: Infile with wildcard path vs Infile with filevar