Hi,
I am using SAS 4.6 client and have 2 SAS tables joined and need to map all columns from both of them to target table.
The problem is: most of the columnn names are the same for two source tables.
I can't add columns from 2nd table after I added all from the first one (only few which differ are added).
Is there a way to add them and for SAS to rename target column names automatically?
Tables have 100+ columns each.
Thanks!
Best regards,
Hi Mario. There’s no ‘automatic’ option to rename 100+ columns to make the variable names mutually exclusive for the two datasets you want to merge (apart from your merge by variables) and I guess your question stems from not wanting to manually code data step option (rename = (old-name-1 = new-name-1 … old-name-100+ = new-name-100+))?! There’s an interesting thread on the topic: https://communities.sas.com/t5/SASware-Ballot-Ideas/Enhanced-RENAME-syntax-to-handle-variable-lists/... A PROC SQL join might be easier to code SELECT TableA.*, TableB.Var1 AS B_Var1… (if you have a listing of the columns in CSV/Excel you can =Concatenate(“,TableB.”,A#,” AS B_”,A#) where column A contains your original variable names).
Hi Mario. There’s no ‘automatic’ option to rename 100+ columns to make the variable names mutually exclusive for the two datasets you want to merge (apart from your merge by variables) and I guess your question stems from not wanting to manually code data step option (rename = (old-name-1 = new-name-1 … old-name-100+ = new-name-100+))?! There’s an interesting thread on the topic: https://communities.sas.com/t5/SASware-Ballot-Ideas/Enhanced-RENAME-syntax-to-handle-variable-lists/... A PROC SQL join might be easier to code SELECT TableA.*, TableB.Var1 AS B_Var1… (if you have a listing of the columns in CSV/Excel you can =Concatenate(“,TableB.”,A#,” AS B_”,A#) where column A contains your original variable names).
Hi,
I expected default behavior similar to SAS EG: SAS appends 1 to the end of the column name that already exist under that name.
Sorry to hear it has to be more/less manual job in DI.
Can you APPEND the data instead, if most of the columns are the same, and then TRANSPOSE it instead of doing the 'manual' approach?
Hi,
Source data I have no influence on.
I did it manual way. Good thing is that this is not a daily basis use case, more as a exception.
Thank you for your hints.
Best regards,
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.