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 April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.