Reading a File List into a Data Set
It is a common requirement to generate a list of files contained within a folder. This can be achieved in SAS using an UNNAMED PIPE statement. The principle behind this technique makes use of SAS's ability to submit native commands directly to the operating system. The following examples are based on a windows operating system.
In order to generate a file listing the DIR command can be used under the command line (CMD.exe). For example, the command 'DIR C:Temp', will list all of the files in the 'C:Temp' folder. SAS can make use of this in the form of an UNAMED PIPE statement; this is used as part of a SAS FILENAME statement, normally used to link to non SAS data files:
filename dirlist PIPE 'dir C:temp';
In the example above the FILEREF created, (dirlist), can be read by a SAS data step as if it were a text file. The data read in will be the text returned by the native DIR command (a list of files and folders). Any options available to the DIR command can be included such as, /B to remove extraneous information. Thus the following program can be used to read a file listing into a SAS data set:
%macro read_data; data dirlist; infile dirlist missover pad; input filename $255.; run; %mend;
filename dirlist PIPE 'dir C:temp /B'; %read_data;
This will work happily until a space is introduced into the file path; the problem lies with the DIR command which requires a path with spaces to be contained in double quotes. This then, requires the following FILENAME statement to be used.
filename dirlist PIPE 'dir "C:TempSpaces in Folder Name"/b '; %readandprint;
There is however yet another potential problem to be solved; it is a likely requirement that the file path be stored within a macro variable. This would lead to a FILENAME statement something like the following (which does not work!):
%let file_path= C:TempSpaces in Folder Name; filename dirlist PIPE "dir "&file_path" /b"; %readandprint;
The problem is that, in order that the macro variable should be resolved SAS requires that it be places within double quotes, the DIR command also requires double quotes to be employed to deal with the spaces in the file path. The ambiguous use of double quotes causes SAS to misinterpret what is intended, resulting in errors. This problem can be easily solved by making use of two sets of double quotes as in the following example:
%let file_path= C:TempSpaces in Folder Name; filename dirlist pipe "dir ""&file_path"" /b "; %readandprint;
In the above example the double quotes that are intended to be used as part of the DIR statement are replaced with two sets of double quotes (""). SAS will now treat each pair of double quotes as simple text (not as quotes) but will pass each pair of double quotes to the operating system as a single set of double quotes. Thus no errors are caused in either SAS or the operating system.
For more information view the SAS help entries or the SAS support website (www.support.sas.com).