SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Mapping columns from multiple tables with same column names to target ( SAS DI 4.6)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Mapping columns from multiple tables with same column names to target ( SAS DI 4.6)

[ Edited ]

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

Accepted Solutions
Solution
‎11-13-2017 04:55 AM
Occasional Contributor
Posts: 5

Re: Mapping columns from multiple tables with same column names to target ( SAS DI 4.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


All Replies
Solution
‎11-13-2017 04:55 AM
Occasional Contributor
Posts: 5

Re: Mapping columns from multiple tables with same column names to target ( SAS DI 4.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).

Occasional Contributor
Posts: 10

Re: Mapping columns from multiple tables with same column names to target ( SAS DI 4.6)

[ Edited ]
Posted in reply to Jeremy_Browne

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
Super User
Posts: 20,226

Re: Mapping columns from multiple tables with same column names to target ( SAS DI 4.6)

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

Super User
Posts: 5,490

Re: Mapping columns from multiple tables with same column names to target ( SAS DI 4.6)

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
Occasional Contributor
Posts: 10

Re: Mapping columns from multiple tables with same column names to target ( SAS DI 4.6)

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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