Read specific data from multiple text files in one directory in SAS

Question

*** Resolved. The answer will be attached in the end. Thank you all for your help!

Thanks for reading this. I am working on a piece of code that was left by my former colleague, and trying to make some sense out of it.

The purpose is to read specific data (ex: value from row 1 & column 17, and row 12 & column 33) from multiple text files in one directory and put the data in a SAS table.

%macro cycle_through_server_records;

%local i next_file;
%do i=1 %to %sysfunc(countw(&SERVER_FILE)) ;
%let next_file = %scan(&SERVER_FILE, &i);
%let Ext =.TXT ;

DATA TEST.LIC_SERVER;
INFILE 'C:\Users\Name\Desktop\TEST\&next_file&..TXT'   FIRSTOBS=1 OBS=12 
MISSOVER;
INPUT
@17 DATA_FILE $11.///////////
@33 SERVER_RECORDS_COUNTS $9. ;
RUN;

PROC APPEND BASE=TEST.SERVER_FILE DATA=TEST.LIC_SERVER FORCE;
RUN;

%END;
%MEND cycle_through_server_records;

%cycle_through_server_records;

The macro variable is set from metadata file via SQL query.

proc sql noprint;
select State 
     , compare_Date
     , Data_Source
     , Source_Date
     , file_name
     , Source_file_date
  into :total_state separated by ' ' 
     , :compare_date separated by ' '
     , :Data_Src separated by ' ' 
     , :Source_Cd_Date separated by ' '
     , :Server_file separated by ' '
     , :Src_file_dt separated by ' ' 
  from AUTO_SDS.config 
; 
quit;

Every time I ran it in SAS, I receive this error message "ERROR: Physical file does not exist, C:\Users\Name\Desktop\TEST\&next_file&..TXT". I am not sure what's wrong with the file path?

Also, with the error message, the code was still able to ran. But returned 9 repeated rows from only one text file (there are multiple text files in the directory). I am stuck. Can someone please help me with resolving the issue?

Thank you for your time and help in advance!

As suggested, I am posting the code I ran and the log file here. Thank you!

%LET Path_files = C:\Users\name\Desktop\TEST;

%macro cycle_through_server_records;
%local i next_file;
%do i=1 %to %sysfunc(countw(&SERVER_FILE)) ;
%let next_file = %scan(&SERVER_FILE, &i);

DATA TEST.LIC_SERVER;
INFILE "&Path_files\&next_file&..TXT"   FIRSTOBS=1 OBS=12 MISSOVER;
INPUT
@17 DATA_FILE $11.///////////
@33 SERVER_RECORDS_COUNTS $9. ;
RUN;

PROC APPEND BASE=TEST.SERVER_FILE DATA=TEST.LIC_SERVER FORCE;
RUN;

%END;
%MEND cycle_through_server_records;

%cycle_through_server_records;

PROC SORT DATA=TEST.SERVER_FILE NODUP;
BY DATA_FILE;
RUN;

There are 22 files in the directory, and their name are all in the following format "State_201708" and they are Text Document (.txt).

The output data set TEST.SERVER_FILE only has one file's data.

SAS log:

There are 8 error messages for 8 files in the directory (I am not sure why it's 8, instead of 21. Since only one file's data got captured successfully). The error message looks like the following:

ERROR: Physical file does not exist, C:\Users\name\Desktop\TEST\State_201708.TXT.

Thanks for your help!

*** Solution With all your comments and a colleague's help, we found the cause of why it only worked for some files in the directory but not all. As Tom pointed out below, it was retrieving information from variable SERVER_FILE (from CONFIG table which is not used here).

What we did to make it work: we added an extra step before the macro to read all the file names from the directory and put them in a SAS table. And then turned that column into a variable. Replace SERVER_FILE with the new variable name. Now it worked!

Answer 1

If you have the list of filename in a dataset you can use that to read the files.

data test.server_file;
  set auto_sds.config;
  length fname $256 ;
  fname = cats("&path_files\",file_name,".txt");
  if fileexist(fname) then do;
    infile in filevar=fname n=12 truncover ;
    input #1 @17 data_file $11. #12 @33 server_records_counts $9. ;
  end;
  else put 'ERROR: File not found ' fname= $quote.;
run;

Or if you want every file then you don't even need to start with a dataset.

%LET Path_files = C:\Users\name\Desktop\TEST;
data test.server_file;
  length fname filename $256 ;
  infile "&path_files\*" filename=fname eov=eov truncover ;
  input @;
  filename = fname ;
  if _n_=1 or eov then do;
    input @17 data_file $11. 
      /////////// @33 server_records_counts $9. 
    ;
    output;
  end;
  eov=0;
run;

Answer 2

You have single quotes around your file path. Macro variables only resolve inside double quotes. Try changing:

'C:\Users\Name\Desktop\TEST\&next_file&..TXT' 

to

"C:\Users\Name\Desktop\TEST\&next_file..TXT"

I removed the extra ampersand, which would have resolved to nothing anyway.