## conditional horizon PROC SQL merging

Solved
Regular Contributor
Posts: 161

# conditional horizon PROC SQL merging

Dear All:

I have a question on how to do conditional PROC SQL merging in the sense that I want the merge occur only if certain criteria is met.

I have to tables

HAVE1 with variables A B X

HAVE2 with variable X Y Z

After merging these two based on X, I get WANT1

WANT1 with variable A B X Y Z.  But some of the observations of Y and Z is MISSING, so I need to get these observation from another table HAVE 3

HAVE3 with variable B Y Z.  I then merge WANT1 with HAVE3 only for those observations with missing Y and Z.

May I ask how to best to do that for the second step of the merge (conditional merge)?  Thank you !

Accepted Solutions
Solution
‎10-17-2013 09:33 AM
Posts: 3,167

## Re: conditional horizon PROC SQL merging

Why not omit all of the subqueries?

proc sql;

create table res1 as

select have1.a, have1.b, have1.x

,coalesce(have2.y,have3.y) as y

,coalesce(have2.z,have3.z) as z

from

have1

full join

have2

on have1.x = have2.x

left join

have3

on have1.b = have3.b

;

quit;

All Replies
Super Contributor
Posts: 391

## Re: conditional horizon PROC SQL merging

How abot this?

data have1;
length a b x \$2;
input a b x;
datalines;
a1 b1 x1
a2 b2 x2
a3 b3 x3
a4 b4 x4
;
run;

data have2;
length x y z \$2;
input x y z;
datalines;
x1 .  z1
x2 y2 .
x3 y3 z3
;
run;

data have3;
length b y z \$2;
input b y z;
datalines;
b1 y5 z5
b2 y6 z6
b3 y7 z7
b4 y8 z8
;
run;

proc sql;
create table res as
select have1.a, have1.b, have1.x
,coalesce(have2.y,have3.y) as y
,coalesce(have2.z,have3.z) as z
from

(select * from have1) as have1
full join
(select * from have2) as have2
on have1.x = have2.x

left join

(select * from have3) as have3
on have1.b = have3.b
;
quit;

Solution
‎10-17-2013 09:33 AM
Posts: 3,167

## Re: conditional horizon PROC SQL merging

Why not omit all of the subqueries?

proc sql;

create table res1 as

select have1.a, have1.b, have1.x

,coalesce(have2.y,have3.y) as y

,coalesce(have2.z,have3.z) as z

from

have1

full join

have2

on have1.x = have2.x

left join

have3

on have1.b = have3.b

;

quit;

Super Contributor
Posts: 391

## Re: conditional horizon PROC SQL merging

Regular Contributor
Posts: 161

## Re: conditional horizon PROC SQL merging

Hats off for the the method.  I'm kind of nervous to use complex SQL join.  Could you tell me why not use inner join instead of full join?

If I have to do it in steps, how would I disaggregate the joinning of different tables?  Thanks !

Posts: 5,519

## Re: conditional horizon PROC SQL merging

Adding an extra line to dataset have2 in Fredrick's example shows you the effect of the full join:

data have1;
length a b x \$2;
input a b x;
datalines;
a1 b1 x1
a2 b2 x2
a3 b3 x3
a4 b4 x4
;
run;

data have2;
length x y z \$2;
input x y z;
datalines;
x1 .  z1
x2 y2 .
x3 y3 z3
x5 . z5
;
run;

data have3;
length b y z \$2;
input b y z;
datalines;
b1 y5 z5
b2 y6 z6
b3 y7 z7
b4 y8 z8
;
run;

proc sql;
create table want as
select
have1.a,
have1.b,
have1.x,
coalesce(have2.y,have3.y) as y,
coalesce(have2.z,have3.z) as z
from
have1 full join
have2 on have1.x = have2.x left join
have3 on have1.b = have3.b
;
select * from want;
quit;

This is probably not what you want. You would do better if you replaced the full join with a left join. Try it.

PG

PG
🔒 This topic is solved and locked.