joing tables using PROC SQL

[willy0625]

So I am trying to combine the two tables given below by using PROC SQL.

DATA Math;

    INPUT name $ mark;

    DATALINES;

A 94

B 92

;

DATA Phy;

    INPUT name $ mark;

    DATALINES;

B 92

C 81

;

This is the output I want:

name mark

  A      94

  B      92

  C      81

But I keep getting

name mark

  A      94

  B      92

  .       81

Any suggestions?

Many thanks

[MichelleHomes]

You need to use the coalesce function on your name variables so that it will return the nonmissing value. Look for this in the sql help...

Cheers,

Michelle

[Oleg_L]

Try this code.

proc sql noprint;
create table new as select coalescec(a.name,b.name) as name length=1, coalesce(a.mark,b.mark) as mark
from math as a full join phy as b on a.name=b.name;
quit;

[Howles]

Since MATH and PHY have the same structure, just use a simple UNION.

create table want as

select * from math

union

select * from phy

;

willy0625 wrote:

So I am trying to combine the two tables given below by using PROC SQL.

DATA Math;

    INPUT name $ mark;

    DATALINES;

A 94

B 92

;

DATA Phy;

    INPUT name $ mark;

    DATALINES;

B 92

C 81

;

This is the output I want:

name mark

  A      94

  B      92

  C      81

But I keep getting

name mark

  A      94

  B      92

  .       81

Any suggestions?

Many thanks

[Ksharp]

Add a little into Howels's code.

Because Howels's union is column matched,

and need to add corresponding to make sure to name matched.

create table want as

select * from math

union corresponding

select * from phy

;

Ksharp