Joining two tables, one with duplicates, some different instances in both

[jnoriega95]

Hello, I'm a relatively new SAS user and I had a question regarding joining two tables, where one table has duplicate account numbers but other different/unique variables making it a new "instance", and another table that only has one account number per instance. The problem I'm having is that there are some account numbers in table 1 that are not in table 2, and some account numbers that are in table 2 but not in table 1. The "full join" and "left join" and "merge" options I've tried have either deleted the duplicate account numbers from the first table, have only taken the ones in one table or the other, or have blank data for one of the table's inputs.

Below is an example of what I have and what I want:

Table1 (4 instances):

AcctID       Variable1     Variable2

1                    x                    y

2                    x                    y

2                    x                    z

3                    x                    y

Table2 (3 instances):

AcctID       Variable3     Variable4

1                    a                    b

2                    a                    b

4                    a                    b

Want (5 instances):

AcctID     Variable1      Variable2      Variable3     Variable4

1                   x                    y                   a                  b

2                   x                    y                   a                  b

2                   x                    z                   a                  b

3                   x                    y                   .                   .

4                   .                     .                    a                  b

Thank you all for the help! 🙂

[Reeza]

FULL JOIN + COALESCE works for me.