sas proc sql join

jeremyyjm — Sat, 14 Apr 2018 14:48:43 GMT

/*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?

Re: sas proc sql join

SuryaKiran — Sat, 14 Apr 2018 15:38:07 GMT

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

Re: sas proc sql join

jeremyyjm — Sat, 14 Apr 2018 15:41:00 GMT

thank you so much Suryakiran

topic sas proc sql join in SAS Programming

sas proc sql join

Re: sas proc sql join

Re: sas proc sql join