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

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

strsljen — Wed, 08 Nov 2017 13:09:49 GMT

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,

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

Jeremy_Browne — Wed, 08 Nov 2017 10:45:17 GMT

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/idi-p/401758 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).

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

strsljen — Wed, 08 Nov 2017 13:10:35 GMT

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.

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

Reeza — Wed, 08 Nov 2017 16:05:40 GMT

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

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

LinusH — Wed, 08 Nov 2017 16:46:44 GMT

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?

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

strsljen — Mon, 13 Nov 2017 09:54:54 GMT

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,