RETRIEVING PARTICULAR RECORDS USING JOINS?

[shravanisreeyan]

Data Have1;                     

input id @@;

cards;

1 1 2 2 3  . .

;

run;

Data Have2;

input id @@;

cards;

1 1 2 2 2 4  . .

;

run;

In above program  . . indicates missing values 

By using joins how can i retrieve 3 , 4  i.e   resultant dataset contain 3, 4  values from id variable.

[Kurt_Bremser · Accepted solution]

data want;
merge
  have1 (in=h1)
  have2 (in=h2)
;
by id;
if not h1 or not h2;
run;

[shravanisreeyan]

Happy with your code. I want to achieve same output using sql joins?

[Kurt_Bremser]

Untested:

proc sql;
create table want as
  select coalesce(h1.id,h2.id) as id
  from have1 h1
  full join have2 h2
  where h1.id is missing or h2.id is missing
;
quit;

[Ksharp]

Data Have1;                     
input id @@;
cards;
1 1 2 2 3  . .
;
run;

Data Have2;
input id @@;
cards;
1 1 2 2 2 4  . .
;
run;

proc sql;
create table want as
(select id from have1 except select id from have2) 
union
(select id from have2 except select id from have1) 
;
quit;