Solved
Contributor
Posts: 49

# What is the equivalent Proc SQL code?

data one;

cards;

101 Murphy’s Sports  115 Main St.

102 Sun N Ski  2106 Newberry Ave.

103 Sports Outfitters  19 Cary Way

104 Cramer & Johnson  4106 Arlington Blvd.

data two;

input id Order;

cards;

102 562.01

104 254.98

104 1642.00

101 3497.56

102 385.30

;

proc sort data=two;

by id;

data three;

merge one two (in=b);

by id;

if b=0;

proc print ;

run;

Accepted Solutions
Solution
‎08-22-2012 05:40 AM
Super Contributor
Posts: 276

## Re: What is the equivalent Proc SQL code?

Hi Devarayalu..

bellow code is alternative for the above data step..

Proc SQL ;

Create Table AntiJoin As

Select *

From One a

Left Join

Two b

On a.id =b.id

Where Missing( b.id ) ;

Quit ;

Regards..

Sanjeev.K

All Replies
Solution
‎08-22-2012 05:40 AM
Super Contributor
Posts: 276

## Re: What is the equivalent Proc SQL code?

Hi Devarayalu..

bellow code is alternative for the above data step..

Proc SQL ;

Create Table AntiJoin As

Select *

From One a

Left Join

Two b

On a.id =b.id

Where Missing( b.id ) ;

Quit ;

Regards..

Sanjeev.K

Contributor
Posts: 49

## Re: What is the equivalent Proc SQL code?

Hi Sanjeev.K

This is left join, but i want all from one except b. Please run the data step once.

Thank you

Contributor
Posts: 49

## Re: What is the equivalent Proc SQL code?

Sorry u are correct......

Super Contributor
Posts: 276

## Re: What is the equivalent Proc SQL code?

Hmm Good..

Just Make my answer as a Correct Answer..That would be Great for me..:-)

Regards.

Sanjeev.K

Super Contributor
Posts: 276

## Re: What is the equivalent Proc SQL code?

Hi..

But You put the condition as IF b=0,

which means you are taking ID's those are  present in ONE but Not in TWO.

Just run your data step and SQL query which i have provided....You will get same result...

Regards..

Sanjeev.K

🔒 This topic is solved and locked.