When you merge two tables (ALPHA and BETA) that contain the same variables (e.g., X, Y, Z) to compare them, SAS©, by default, overwrites the values of the first table with those of the second.
To avoid this and calculate differences (e.g., X_Diff = X - X_Beta), you are often forced to write an endless RENAME statement:
| 1 | /* La méthode laborieuse */ |
| 2 | DATA test; |
| 3 | MERGE alpha |
| 4 | beta(rename=(X=X_Beta Y=Y_Beta Z=Z_Beta ...)); /* Fastidieux si 50 variables ! */ |
| 5 | BY id; |
| 6 | Diff_X = X - X_Beta; |
| 7 | RUN; |
This is often the cleanest alternative. Unlike the Data Step, PROC SQL allows you to handle two columns with the same name by prefixing them with their source table (alias).
You don't need to rename the variables before the calculation; you do it on the fly.
| 1 | PROC SQL; |
| 2 | create TABLE test as |
| 3 | select a.id, |
| 4 | a.X, |
| 5 | b.X as X_Beta, /* Renommage simple pour la sortie */ |
| 6 | (a.X - b.X) as Diff_X, /* Calcul direct sans renommage préalable */ |
| 7 | (a.Y - b.Y) as Diff_Y |
| 8 | from alpha as a, |
| 9 | beta as b |
| 10 | where a.id = b.id; |
| 11 | QUIT; |
If your goal is solely to compare the tables to see the differences, stop reinventing the wheel with a Data Step! SAS© has a procedure designed exactly for this, which handles identical names automatically.
| 1 | PROC COMPARE base=alpha compare=beta; |
| 2 | id id; |
| 3 | var X Y Z; |
| 4 | RUN; |
If you absolutely insist on using a Data Step MERGE and you have 100 variables to rename, the solution is to generate the RENAME=(...) list dynamically via a Macro.
The principle is as follows:
| 1 | %macro auto_rename(lib, ds, suffix); |
| 2 | /* Code simplifié pour l'exemple */ |
| 3 | PROC SQL noprint; |
| 4 | select catx('=', name, catx('_', name, "&suffix")) |
| 5 | into :renamelist separated BY ' ' |
| 6 | from dictionary.columns |
| 7 | where LIBNAME=upcase("&lib") and memname=upcase("&ds"); |
| 8 | QUIT; |
| 9 | /* Renvoie la chaîne générée */ |
| 10 | &renamelist |
| 11 | %mend; |
| 12 | |
| 13 | /* Utilisation */ |
| 14 | DATA test; |
| 15 | MERGE alpha |
| 16 | beta(rename=( %auto_rename(WORK, BETA, Beta) )); |
| 17 | BY id; |
| 18 | RUN; |
| Need | Recommended Solution |
| Calculate differences on a few variables | PROC SQL (Use aliases a.var - b.var). |
| Check if two tables are identical | PROC COMPARE (It's designed for that). |
| Merge 50+ identical variables into one table |
Sometimes, the difficulty of doing something in Base SAS© is a sign that it shouldn't be done that way." If you spend an hour writing a massive rename statement, it's likely that PROC COMPARE was the solution all along.
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.