DATA Step, Macro, Functions and more

sas proc sql join

Reply
Occasional Contributor
Posts: 5

sas proc sql join

/*method1*/

data exe1;

input id $ dep $ salary;

cards;

1003 bd .

;

data exe2;

input id $ dep $ salary;

cards;

1001 ac 2100

1003 bd 2900

;

proc sql;

select exe1.id, exe1.dep,exe2.salary

from exe1 left join exe2

on exe1.id=exe2.id

and exe1.dep=exe2.dep;

quit;

/*method2*/

data e1;

input id $ dep $ age ;

cards;

1001 ac 23

1002 ac 25

1001 bk 19

1003 bd 22

;

data e2;

input id $ dep $ salary;

cards;

1001 ac 2300

1002 ac 2500

1001 bk 1900

;

data e3;

input id $ dep $ salary;

cards;

1001 ac 2100

l003 bd 2900

;

proc sql;

create table a as

select e1.id,e1.dep,salary

from e1 left join e2

on e1.id=e2.id

and e1.dep=e2.dep

where salary is not null;

quit;

proc sql;

create table b as

select e1.id,e1.dep,salary

from e1 left join e2

on e1.id=e2.id

and e1.dep=e2.dep

where salary is null;

quit;

proc sql;

select b.id,b.dep,e3.salary

from b left join e3

on b.id=e3.id

and b.dep=e3.dep;

quit;

 

Can someone help me, why there are two different results from method 1 and method 2?

Valued Guide
Posts: 597

Re: sas proc sql join

Posted in reply to jeremyyjm

Check your e3 dataset. It's not 1003 you have there 'l' Letter 'L' instead of one '1'.

Capture.PNG

Thanks,
Suryakiran
Occasional Contributor
Posts: 5

Re: sas proc sql join

Posted in reply to SuryaKiran

thank you so much Suryakiran

Ask a Question
Discussion stats
  • 2 replies
  • 123 views
  • 2 likes
  • 2 in conversation