topic Detect files and create one append table in SAS Programming

Detect files and create one append table

FP12 — Thu, 13 Jul 2017 16:36:53 GMT

Hi all,

I have a directory &dir, with several files in txt in it. Some files begin by "FILE_" others don't.
The files beginning by "FILE_" have the same format, "hopefully"
A $6
B $12

I just need to create a table Z, as an append of all files beginning by "FILE_" whatever is the number of files in the directory dir.

I am a bit lost on how to do that.
Can someone help me on 'easy' problem (I am a SAS almost beginner)?

Thanks

Re: Detect files and create one append table

mkeintz — Thu, 13 Jul 2017 18:09:53 GMT

You could use sas functions to open a directory and get a list of file names in that directory, but this problem is also amenable to using an operating system command to trivially list the desired files names. That list can be put in an %include file, as in:

filename mypipe pipe "dir/b c:\temp\FILE_*.txt";
filename tmp  temp ;

data _null_;
  infile mypipe truncover end=eod;
  input filnam $50.;
  filnam=cats('c:\temp\',filnam);

  file tmp;
  if _n_=1 then put 'infile "(' @;
  else          put ',' @ ;
  put filnam @;
  if eod then put ')";';
run;

options source2;
data want;
  %include tmp ;
  *input x y;
  input (a b) ($6. $12.);
run;

Notes:

This program assumes a windows environment (hence the use of the "dir/b" command -- i.e. a "bare" directory list of all txt files that have names beginning with "FILE_".
That dir command is embedded within a filename statement of type "pipe" - i.e. pipe the output of the dir command when the filename reference MYPIPE is used.
The "filename tmp temp" established a temporary external file - i.e. it will be deleted when the sas session is finished. You don't need to know where the file is located. You're just going to write stuff to it, and read it back when needed during the session.
The first data step reads in the piped list of file names, prepends the directory name and writes an INFILE statement with a comma-separated list of full names to TMP. The result will look like this:
infile "(c:\temp\FILE_001.txt ,c:\temp\FILE_002.txt)" ;
The second data step uses "%include tmp" to read in TMP as one or more SAS statements. Note the "option source2" which tells sas to print to log the content of the %include files .

Re: Detect files and create one append table

FP12 — Mon, 17 Jul 2017 14:46:40 GMT

Hello mkeintz,

Thanks for the reply.

I got an issue when I try to "include tmp":

ERROR: Physical file does not exist, /apps/sas94/sasconfig/Lev1/SASMain/c:\temp/usr/bin/ksh:.

I am indeed working on Windows.

I think it means the file filenam wasn't created as planned in the first datastep?

I don't really understand the path file because you are mixing a SAS path with a Windows path. Is it normal?

Or maybe I am not granted to write (I see a Lev1 in the path that's why I think this)?

Thanks