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

jnoriega95 — Thu, 29 Apr 2021 20:16:47 GMT

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! 🙂

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

Reeza — Thu, 29 Apr 2021 20:39:20 GMT

FULL JOIN + COALESCE works for me.

topic Joining two tables, one with duplicates, some different instances in both in New SAS User

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

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