In the SAS© universe, it is common to have to consolidate historical data stored in the same library. While the operation is trivial with standardized file names (e.g., DATA_202301, DATA_202302), it becomes perilous when the files come from Excel imports or legacy systems using spaces, symbols ($, &), or "free text" formats.
This article shows you how to dynamically concatenate the entire content of a library, even when the table names seem impossible to handle programmatically.
Let's take the example of a library named RH_ARCHIVE. It contains monthly exports named very literally, including spaces and special characters:
'JANVIER 2024 STAFFING$'
'FEVRIER 2024 - HEURES SUP'
'MARS 2024 & PRIMES'
If you try a classic approach by retrieving the list of tables via dictionary.tables to inject it into a DATA step, SAS© will fail.
Why? Because SAS© interprets 'MARS 2024 & PRIMES' as three distinct objects (MARS, 2024, &, PRIMES). Moreover, without an explicit library reference, it will look for these tables in WORK.
You will inevitably get the error:
To handle these tables, SAS© requires a specific syntax called a "Name Literal", which looks like this: 'Table Name'n. It is also imperative to prefix the library name.
Rather than writing the code by hand for each file, we will use PROC SQL to generate the exact syntax.
The objective is to build a character string that looks like:
RH_ARCHIVE.'JANVIER 2024 STAFFING$'n
Here is the code to automate this using the CATT function:
| 1 | /* 1. Construction de la liste des tables avec la syntaxe Name Literal */ |
| 2 | PROC SQL; |
| 3 | SELECT catt('RH_ARCHIVE.', quote(MEMNAME), 'n') |
| 4 | INTO :LISTE_A_FUSIONNER SEPARATED BY ' ' |
| 5 | FROM dictionary.tables |
| 6 | WHERE LIBNAME = 'RH_ARCHIVE'; |
| 7 | QUIT; |
| 8 | |
| 9 | /* 2. Contrôle du résultat dans la log */ |
| 10 | %PUT &=LISTE_A_FUSIONNER; |
| 11 | |
| 12 | /* 3. Fusion des données */ |
| 13 | DATA CONSOLIDATION_RH; |
| 14 | SET &LISTE_A_FUSIONNER.; |
| 15 | RUN; |
quote(MEMNAME): This function surrounds the retrieved name (e.g., MARS 2024 & PRIMES) with double quotes. This "protects" the spaces and special characters.
'n': By appending this suffix after the quotes, we indicate to the SAS© engine that this is not a text string, but a valid table name (a literal).
'RH_ARCHIVE.': We force the location of the source. Without this, SAS© would not find the physical file.
This technique is universal: it works regardless of the "cleanliness" of your source file names. However, for your future tables, always prefer the standard SAS© naming convention (no spaces, use of the underscore _, no special characters) to facilitate the maintenance of your programs.
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.