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.
%macro jig(name);
data datdir.NEW_&name;
/* Attention : les deux tables doivent être triées par ID au préalable */
merge excelsheetdata datdir.&name;
by ID;
run;
%mend jig;
1
%macro jig(name);
2
DATA datdir.NEW_&name;
3
/* Attention : les deux tables doivent être triées par ID au préalable */
proc sql;
create table liste_tables as
select memname
from sashelp.vtable
where libname='DATDIR' /* Nom de la lib en MAJUSCULES */
and memname like 'DATA_%'; /* Filtre optionnel si besoin */
quit;
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 metadata for all active tables (name, number of observations, creation date, etc.).
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.
data _null_;
set liste_tables;
/* Construction de la chaîne : '%jig(TABLE_1)' */
/* On utilise STRIP pour enlever les espaces inutiles du nom */
commande = '%jig(' || strip(memname) || ')';
/* Exécution immédiate de cette commande */
call execute(commande);
run;
1
DATA _null_;
2
SET liste_tables;
3
4
/* Construction de la chaîne : '%jig(TABLE_1)' */
5
/* On utilise STRIP pour enlever les espaces inutiles du nom */
6
commande = '%jig(' || strip(memname) || ')';
7
8
/* Exécution immédiate de cette commande */
9
call execute(commande);
10
RUN;
3. Syntax Analysis of CALL EXECUTE
In the solution proposed by WeAreCas, you will notice a slightly complex syntax:
Don't forget that for a MERGE to work, all your tables (all 50 of them!) must be sorted by the key variable (ID) beforehand. If not, you will need to add a PROC SORT inside your %jig macro before the Data step.
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.
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.
This site uses technical and analytical cookies to improve your experience.
Read more.
