Automating the Processing of Multiple Tables in a Folder

A common challenge for SAS^© analysts is having to repeat an operation (like a MERGE to add a column) on dozens of tables located in the same directory. Writing the macro call %jig(name=AB);, %jig(name=AC);... 50 times is tedious and prone to errors.

How can you make SAS^© "scan" the folder and automatically apply the macro to each file? Here is the elegant method proposed by WeAreCas, using SAS^© metadata.

1. The Strategy: Dictionaries and `CALL EXECUTE`

The approach relies on two pillars of dynamic SAS^© programming:

Retrieve the list of names: Use dictionary views (SASHELP.VTABLE) to automatically list everything in a library.
Generate the code dynamically: Use the CALL EXECUTE statement in a Data Step to run the macro for each name found.

2. The Code Step by Step

Step 0: Define the Processing Macro

This is the code the user wants to execute for a single table.

1	%macro jig(name);
2	DATA datdir.NEW_&name;
3	/* Attention : les deux tables doivent être triées par ID au préalable */
4	MERGE excelsheetdata datdir.&name;
5	BY ID;
6	RUN;
7	%mend jig;

Step 1: List the Target Tables (PROC SQL)

Instead of writing the names by hand, we ask SAS^©: "Give me the list of all non-empty tables present in the 'DATDIR' library."

1	PROC SQL;
2	create TABLE liste_tables as
3	select memname
4	from sashelp.vtable
5	where LIBNAME='DATDIR' /* Nom de la lib en MAJUSCULES */
6	and memname like 'DATA_%'; /* Filtre optionnel si besoin */
7	QUIT;

Note: SASHELP.VTABLE contains the metadata for all active tables.

Note :
Step 2: Automation (CALL EXECUTE)
This is where the magic happens. We loop through the list created in Step 1, and for each row, we build a text command that calls the macro.

1	DATA _null_;
2	SET liste_tables;
3
4	/* Construction de la chaîne : '%jig(TABLE_1)' */
5	commande = '%jig(' \|\| strip(memname) \|\| ')';
6
7	/* Exécution immédiate de cette commande */
8	call execute(commande);
9	RUN;

3. Syntax Analysis of `CALL EXECUTE`

In the solution proposed by WeAreCas, you will notice a somewhat complex syntax:

call execute( '%nrstr( %%)' !! 'jig(' !! memname !! ') ' );

Why %nrstr(%%)?

CALL EXECUTE resolves macros during the execution of the Data Step.
Sometimes, you want to delay this execution or protect certain special characters.
In most simple cases (like this one), call execute('%jig(' || memname || ')'); is sufficient.
Adding %nrstr is often used to prevent SAS^© from trying to resolve the macro too early if it contains conflicting macro variables.

Summary of the Method

Method	Advantage	Disadvantage
Manual	Simple to understand.	Long, repetitive, risk of omission.
Wildcards (e.g., `DATA_:` )	Fast for a simple SET.	Does not work for a complex MERGE.
Dictionaries + Call Execute	Fully automatic, flexible.	Requires knowledge of SQL and meta-programming.

Technical advice: Don't forget that for a MERGE to work, all your tables (all 50 of them!) must be previously sorted by the key variable (ID). If this is not the case, you will need to add a PROC SORT inside your %jig macro.

Important Disclaimer

The codes and examples provided on WeAreCAS.eu are for educational purposes. It is imperative not to blindly copy-paste them into your production environments. The best approach is to understand the logic before applying it. We strongly recommend testing these scripts in a test environment (Sandbox/Dev). WeAreCAS accepts no responsibility for any impact or data loss on your systems.

Back to article list

Automating the Processing of Multiple Tables in a Folder

Niveau de difficulté

Published on : 20/12/2012

1. The Strategy: Dictionaries and `CALL EXECUTE`

2. The Code Step by Step

Step 0: Define the Processing Macro

Step 1: List the Target Tables (PROC SQL)

3. Syntax Analysis of `CALL EXECUTE`

Summary of the Method

Important Disclaimer

Niveau de difficulté

Published on : 20/12/2012

1. The Strategy: Dictionaries and CALL EXECUTE

2. The Code Step by Step

Step 0: Define the Processing Macro

Step 1: List the Target Tables (PROC SQL)

3. Syntax Analysis of CALL EXECUTE

Summary of the Method

Important Disclaimer

1. The Strategy: Dictionaries and `CALL EXECUTE`

3. Syntax Analysis of `CALL EXECUTE`