Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

What is the equivalent Proc SQL code?

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

What is the equivalent Proc SQL code?

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;


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

Re: What is the equivalent Proc SQL code?

Posted in reply to devarayalu

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

View solution in original post


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

Re: What is the equivalent Proc SQL code?

Posted in reply to devarayalu

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?

Posted in reply to kuridisanjeev

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?

Posted in reply to devarayalu

Sorry u are correct......

Super Contributor
Posts: 276

Re: What is the equivalent Proc SQL code?

Posted in reply to devarayalu

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?

Posted in reply to devarayalu

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 995 views
  • 0 likes
  • 2 in conversation