topic Re: joing tables using PROC SQL in SAS Procedures

joing tables using PROC SQL

willy0625 — Thu, 28 Jul 2011 04:06:01 GMT

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

joing tables using PROC SQL

MichelleHomes — Thu, 28 Jul 2011 05:32:29 GMT

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

Re: joing tables using PROC SQL

Oleg_L — Thu, 28 Jul 2011 07:20:15 GMT

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;

joing tables using PROC SQL

Howles — Thu, 28 Jul 2011 14:31:20 GMT

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

joing tables using PROC SQL

Ksharp — Wed, 03 Aug 2011 05:35:36 GMT

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