## proc sql question

Frequent Contributor
Posts: 87

# proc sql question

Hi,
I'm trying to compare and learn proc sql joins to data step merge. I know proc sql code for Left, Right and Inner join and its associated data merge code.
I want to know how do we code the below data step logic in proc sql. Help on the ones that are not commented.

data x;
merge one(in=a) two(in=b);
if a = 1 /* Left Join */
if b = 1 /* Right Join */
if (a=1 and b=1) /* Inner join */
if (a=0 and b=1)
if (a=1 and b=0)
if a + b
Super User
Posts: 13,517

## Re: proc sql question

> Hi,
> I'm trying to compare and learn proc sql joins to
> data step merge. I know proc sql code for Left, Right
> and Inner join and its associated data merge code.
> I want to know how do we code the below data step
> logic in proc sql. Help on the ones that are not
> commented.
>
> data x;
> merge one(in=a) two(in=b);
> if a = 1 /* Left Join */
> if b = 1 /* Right Join */
> if (a=1 and b=1) /* Inner join */
Operations, not quite joins if I understand what you are attempting.
I think the first two here might be EXCEPT operations, order is important in EXCEPT so both are same type.
> if (a=0 and b=1)
> if (a=1 and b=0)
If either set contributies this is a UNION operation, but you need to decide if you want OUTER (a=1 or b=1) or an EXCLUSIVE UNION (a=1 or b=1 but not a=1 and b=1)
> if a + b
Super User
Posts: 10,770

## Re: proc sql question

Need to illustrate sth that merge statement is a little different with proc sql join.
[pre]
Assuming there are not duplicated observations for by variable (or key variable)

if a = 1 /* Left Join */
is identified with left join in proc sql. Such as : proc sql;......from a left join b on ....

if b = 1 /* Right Join */
is identified with rightjoin in proc sql. Such as : proc sql;......from a right join b on ....

if (a=1 and b=1) /* Inner join */
is identified with inner join in proc sql. Such as : proc sql;......from a inner join b on ....

if (a=0 and b=1) is identified with if b = 1
if (a=1 and b=0) is identified with if a=1
Because in merge statement, there are only three type with in-variable
a b
1 0
0 1
1 1

So if a + b is identified with all these three type
So it is the same as
data x;
merge one(in=a) two(in=b);
by ...;
run;

Hope you are clarified more.

Ksharp
Discussion stats
• 2 replies
• 770 views
• 0 likes
• 3 in conversation