topic Re: Merge in SAS Programming

Merge with same named columns

HeatherNewton — Fri, 19 Jan 2024 07:53:29 GMT

If i merge tableA and tableB by account
Let say apart from column account, both table A and table B both have columns salary and age. For the same account in tableA and tableB, salary of table A is different from salary of table B, age of tableA is different from tableB, after merging what would happen? Which salary and age will be in the resulting row?

Re: Merge

LinusH — Thu, 18 Jan 2024 11:56:25 GMT

TableB columns will overwrite TableA columns with the same name.

Unless this is what you want, avoid this situation, e.g., by using the rename= data set option on one or more input data sets.

Re: Merge

Quentin — Thu, 18 Jan 2024 14:49:20 GMT

This is a good experiment to work through with example data. After doing it with a one-to-one merge, and noting the results, you might also try it with a one-to-many merge, and note how the results are different. To really understand MERGE, and how it handles these types of variable collisions, you need to think in terms of the program data vector.

Also, if you set system option MSGLEVEL=i , you will get an INFO: message in the log warning you when a collision happens, but the message itself is a bit misleading as to the complexity of the results from a collision.

Re: Merge

Kurt_Bremser — Thu, 18 Jan 2024 21:48:02 GMT

@HeatherNewton wrote:
.....
Which salary and age will be in the resulting row?

It depends.

In a one-to-one and a one-to-many MERGE, the values of the "right" dataset will overwrite those of the "left".

In a many-to-one MERGE, in the first observation of a group the same as above will happen, but in the following observations, the "left" dataset will win.

In a many-to-many MERGE, the behavior depends on which dataset runs out of observations first.

Bottom line: don't do this without additional code that forces which values will end up in the result, so the intention of the programmer becomes clear.