BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
strsljen
Obsidian | Level 7

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,

 

 

--
Mario
1 ACCEPTED SOLUTION

Accepted Solutions
Jeremy_Browne
Fluorite | Level 6

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).

View solution in original post

5 REPLIES 5
Jeremy_Browne
Fluorite | Level 6

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).

strsljen
Obsidian | Level 7

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.

--
Mario
Reeza
Super User

Can you APPEND the data instead, if most of the columns are the same, and then TRANSPOSE it instead of doing the 'manual' approach?

LinusH
Tourmaline | Level 20
TRANSPOSE is the anti matter when it comes to ETL!
Knot knowing your design, but unless you are dealing with ABT's hundreds of columns indicates an inappropriate model.
Also, what kind of join do you have when you want like named columns duplicated?
Data never sleeps
strsljen
Obsidian | Level 7

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,

 

--
Mario

sas-innovate-2024.png

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.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 5 replies
  • 3538 views
  • 0 likes
  • 4 in conversation