SAS9

Automating the Processing of Multiple Tables in a Folder

Simon 14 vistas
Niveau de difficulté
Débutant
Publicado el :
Dans : SAS9
SAS Tutorial: Mastering Data Sorting with PROC SORT
26/01/2024 • 22
Optimizing PROC SUMMARY Performance in SAS Enterprise Guide
26/04/2023 • 13
Mastering TRIM and Concatenation
20/01/2023 • 21
How to get weighted counts with PROC SUMMARY?
26/08/2022 • 13
How to select and read the Nth file in a Unix directory with SAS
26/08/2022 • 14
Ver toda la categoría

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:

  1. Retrieve the list of names: Use dictionary views (SASHELP.VTABLE) to automatically list everything in a library.

  2. 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
 
1PROC 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 */
7QUIT;
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.
1DATA _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);
9RUN;

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

MethodAdvantageDisadvantage
ManualSimple 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 ExecuteFully 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.

Volver a la lista de artículos
Copyright © WeAreCAS 2014-2026
SAS y todos los demás nombres de productos o servicios de SAS Institute Inc. son marcas registradas o marcas comerciales de SAS Institute Inc. en los EE. UU. y otros países. ® indica registro en los EE. UU. WeAreCAS es un sitio comunitario independiente y no está afiliado a SAS Institute Inc.