What is the equivalent Proc SQL code?

[devarayalu]

data one;

input id Address $5-42 ;

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. 

105 Sports Savers  2708 Broadway 

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;

[kuridisanjeev · Accepted solution]

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

[devarayalu]

Hi Sanjeev.K

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

Thank you

[devarayalu]

Sorry u are correct......

[kuridisanjeev]

Hmm Good..

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

Regards.

Sanjeev.K

[kuridisanjeev]

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