sql join

[shivamarrora0]

Hi All,

Can you please help me getting the below output using sql join

[Shmuel]

It seems that table b updates table a:

data nn;

 update a b;

   by id;

run;

[PeterClemmensen]

What is the logic behind the first row in your want dataset?

[RW9]

Please post your test data as text in a code window (thats the {i} above post area) in future.  For an SQL solution:

proc sql;
  create table WANT as
  select    COALESCE(A.ID,B.ID) as ID,
            COALESCE(A.NAME,B.NAME) as NAME
  from      A
  full join B
  on        A.ID=B.ID;
quit;

[shivamarrora0]

data a;
input id name$;
cards;
1 a
2 b
;
data b;
input id name$;
cards;
1 c
3 d
;

[RW9]

Yep, my code works then.

[shivamarrora0]

i am really sorry the desired output i need ,

1 c
2 b
3 d



sorry for the trouble

[RW9]

Yuo would be best off clarify the logic behind why a data item appears in the output, if it is take B value if present else A then swap the coalesce over:

proc sql;
  create table WANT as
  select    COALESCE(A.ID,B.ID) as ID,
            COALESCE(B.NAME,A.NAME) as NAME
  from      A
  full join B
  on        A.ID=B.ID;
quit;

[Kurt_Bremser]

A data step variant that leaves the name for ID 1 intact:

data nn;
merge
  a
  b (rename=(name=_name))
;
by id;
if missing(name) then name = _name;
drop _name;
run;

[shivamarrora0]

I am really sorry Folks, the desired output is 1 c 2 b 3 d