turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- conditional horizon PROC SQL merging

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2013 03:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FredrikE

10-17-2013 09:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to caveman529

10-17-2013 04:44 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FredrikE

10-17-2013 09:33 AM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Haikuo

10-17-2013 09:41 AM

Just a bad habit.....

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FredrikE

10-19-2013 10:33 PM

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 !

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to caveman529

10-19-2013 10:59 PM

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