To write SAS macro to import multiple txt files with sequentially incremental name into one database

[AZFXL]

I have multiple textfiles with sequentially incremental names, say test_1, test_2, ..., test_1000, each of them have exactly the same format (i.e. number of columns and delimited by comma). My intention is to append all these textfiles into one SAS database following the sequence of their filename. However, with the coding that I am currently using (as attached below), despite it will append all the textfiles, but the appending sequence is not as per my expectation (i.e, it will append test_1,test_10,...,test_19,test_100,...,test_199,test_1000,test_2,test_20,...,test_29,test_200,...,test_299,test_3,test_30,...,test_39,test_300,...,test_399,... instead of test_1, test_2, test_3, ..., test_1000). 

As such, I am trying to explore the feasibility of using SAS macro to get the intended result. Appreciate if I could get any advice from the community to start off with the SAS macro, thank you very much in advance. 

data libname.test;
	infile "&dlpath.\test_*.txt"
		delimiter=','
		missover
		firstobs=1
		DSD
		lrecl = 32767;
	format column1 $12.;
	format column2 yymmdds10.;
	format column3 best12.;
	input
		column1 $
		column2 :yymmdd8.
		column3;
run;

[PaigeMiller]

Can't you just sort the information by file name after SAS does all of this appending? You can use the FILENAME= option of the INFILE statement to create a variable in the SAS data set with the name of the original file.

[Tom · Accepted solution]

So you are saying you want sort by the numeric suffix on the filename instead of sorting on the full filename?

Do the files already have a variable that contains that number from the filename?  Or do you need to create it?

There should not be any need for macro coding to solve this, since there is no need to generate code.

You can use the FILENAME= option to get the name of the file currently being read.  You could then extract the number from that.

data libname.test;
  length fname $256;
  infile "&dlpath.\test_*.txt" dsd truncover filename=fname;
  length filenum row 8 column1 $12 column2 8 column3 8;
  format column2 yymmdds10.;
  informat column2 yymmdd.;
  input column1 -- column3;
  if fname ne lag(fname) then row=0;
  row+1;
  filenum=input(scan(fname,-2,'._'),32.);
run;

proc sort data=libname.test ;
  by filenum row;
run;

Or you could generate a list of file names and use the FILEVAR= option of the INFILE statement instead.  Make sure to use the END= option also.  So if you had a dataset named FILELIST with a variable named FILENAME that had the files in the order you want to read them then just use a data step like this to read all of the files in that order.

data libname.test;
  set filelist;
  infile TXT filevar=filename dsd truncover end=eof;
  do while not(eof);
      ..... Use the same code to define variables and INPUT one line ....
     output;
  end;
run;

[Reeza]

What about sorting the file after the fact instead?

The filename variable is character so that's a character ordering, test_1, test_100. 

You can capture the filename in a variable and then extract the number. Then sort by that number.

data libname.test;
	infile "&dlpath.\test_*.txt"
		delimiter=','
		missover
		firstobs=1
		DSD
		lrecl = 32767 filename=filevar;
	format column1 $12.;
	format column2 yymmdds10.;
	format column3 best12.;
       file_name=filevar;
      *this may need to be modified to capture the number based on the full path;
       file_num=input(compress(file_name, , 'kd'), 8.);
	input
		column1 $
		column2 :yymmdd8.
		column3;
run;

proc sort data=libname.test;
by file_num column1 column2 column3;
run;

---

@AZFXL wrote:

I have multiple textfiles with sequentially incremental names, say test_1, test_2, ..., test_1000, each of them have exactly the same format (i.e. number of columns and delimited by comma). My intention is to append all these textfiles into one SAS database following the sequence of their filename. However, with the coding that I am currently using (as attached below), despite it will append all the textfiles, but the appending sequence is not as per my expectation (i.e, it will append test_1,test_10,...,test_19,test_100,...,test_199,test_1000,test_2,test_20,...,test_29,test_200,...,test_299,test_3,test_30,...,test_39,test_300,...,test_399,... instead of test_1, test_2, test_3, ..., test_1000). 

As such, I am trying to explore the feasibility of using SAS macro to get the intended result. Appreciate if I could get any advice from the community to start off with the SAS macro, thank you very much in advance. 

data libname.test;
	infile "&dlpath.\test_*.txt"
		delimiter=','
		missover
		firstobs=1
		DSD
		lrecl = 32767;
	format column1 $12.;
	format column2 yymmdds10.;
	format column3 best12.;
	input
		column1 $
		column2 :yymmdd8.
		column3;
run;

---