BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7
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
2 REPLIES 2
ballardw
Super User
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
Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1609 views
  • 0 likes
  • 3 in conversation