how to deal missing value using FULL join?

[walterwang]

I have

A:

Name score1

a1 80

a2 70

a3 60

B: name score2

a1 70

a3 60

a4 50

What I want to get is:

name score1 score2

a1 80 70

a2 70 .

a3 60 60

a4 . 50

but if I use full join

what I get is:

name score1 score2

a1 80 70

a2 70 .

a3 60 60

. . 50

how to solve this issue?

[Kurt_Bremser · Accepted solution]

Please post your code.

You will need to use the COALESCE function for the name column from both tables.

Given your data as posted, a DATA step is much better:

data want;
merge a b;
by name;
run;

---

@walterwang wrote:

I have

A:

Name score1

a1 80

a2 70

a3 60

B: name score2

a1 70

a3 60

a4 50

What I want to get is:

name score1 score2

a1 80 70

a2 70 .

a3 60 60

a4 . 50

but if I use full join

what I get is:

name score1 score2

a1 80 70

a2 70 .

a3 60 60

. . 50

how to solve this issue?

---