topic What is the equivalent Proc SQL code? in SAS Data Science

What is the equivalent Proc SQL code?

devarayalu — Wed, 22 Aug 2012 09:24:00 GMT

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;

Re: What is the equivalent Proc SQL code?

kuridisanjeev — Wed, 22 Aug 2012 09:40:30 GMT

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

Re: What is the equivalent Proc SQL code?

devarayalu — Wed, 22 Aug 2012 09:44:35 GMT

Hi Sanjeev.K

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

Thank you

Re: What is the equivalent Proc SQL code?

devarayalu — Wed, 22 Aug 2012 09:53:44 GMT

Sorry u are correct......

Re: What is the equivalent Proc SQL code?

kuridisanjeev — Wed, 22 Aug 2012 09:59:50 GMT

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

Re: What is the equivalent Proc SQL code?

kuridisanjeev — Wed, 22 Aug 2012 10:01:34 GMT

Hmm Good..

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

Regards.

Sanjeev.K