topic Re: proc sql question in SAS Procedures

proc sql question

helloSAS — Tue, 31 May 2011 20:05:20 GMT

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

Re: proc sql question

ballardw — Tue, 31 May 2011 23:02:31 GMT

Please see below.
> 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

Re: proc sql question

Ksharp — Thu, 02 Jun 2011 01:26:47 GMT

Need to illustrate sth that merge statement is a little different with proc sql join.
So.For your question.
[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