conditional horizon PROC SQL merging

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

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
Respected Advisor
Posts: 3,156

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;

View solution in original post


All Replies
Regular Contributor
Posts: 191

Re: conditional horizon PROC SQL merging

Posted in reply to caveman529

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
Respected Advisor
Posts: 3,156

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;

Regular Contributor
Posts: 191

Re: conditional horizon PROC SQL merging

Just a bad habit..... Smiley Happy

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 !

Respected Advisor
Posts: 4,930

Re: conditional horizon PROC SQL merging

Posted in reply to caveman529

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 394 views
  • 6 likes
  • 4 in conversation