Processing multiple files sequentially in SAS

Question

I have a program that starts with:

%let filename = file1.csv

The program then imports the file into a sas dataset and then moves some of the data to a sql table based on some rules.

I want to have the program loop through processing whatever files are in a folder..I guess by redefining filename each time it gets to the end of the program and going back to the top until all of the csv files in the folder have been processed?

How to do it depends on how your existing code works. For example if all of the files have the same structure you might just change the "import" step to read all of the CSV files into a single SAS dataset and not need any looping. — Tom

Tom Tom · Accepted Answer · 2019-02-08T20:23:27

This is an example of a process that might benefit from creating a macro. Sounds like your existing code is close to being ready to become a macro. Just wrap the existing code into a macro definition that takes FILENAME as a parameter (remove your %let statement).

Then your existing program can become something like this. Where the last line is the one that actually runs the steps defined in the macro definition.

%macro loadfile(filename=);
   ... existing code ....
%mend loadfile;
%loadfile(filename=file1.csv);

To extend it to loading all files in a directory you just need to generate the list of files and use the list to generate a series of calls to the macro. So something like this might work on a Windows machine. So it will call the Windows command DIR to get the list of files and read the result into a variable and for each file found generate a call to the macro. The commands pushed by CALL EXECUTE will run after the data step finishes.

data _null_;
   infile 'dir /b *.csv' pipe truncover ;
   input filename $256. ;
   call execute(cats('%nrstr(loadfile)(filename=',filename,')'));
run;

Processing multiple files sequentially in SAS

1 Answers