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
- /
- SAS Procedures
- /
- proc sql question

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2011 04:05 PM

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

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

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

Posted in reply to helloSAS

05-31-2011 07:02 PM

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

> 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

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

Posted in reply to helloSAS

06-01-2011 09:26 PM

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

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