topic Re: sql join in SAS Procedures

sql join

shivamarrora0 — Tue, 01 Aug 2017 09:15:05 GMT

Hi All,

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

Re: sql join

Shmuel — Tue, 01 Aug 2017 09:28:12 GMT

It seems that table b updates table a:

data nn;

update a b;

by id;

run;

Re: sql join

PeterClemmensen — Tue, 01 Aug 2017 09:32:13 GMT

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

Re: sql join

RW9 — Tue, 01 Aug 2017 09:32:45 GMT

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;

Re: sql join

Kurt_Bremser — Tue, 01 Aug 2017 09:47:55 GMT

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;

Re: sql join

shivamarrora0 — Tue, 01 Aug 2017 09:51:27 GMT

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

Re: sql join

RW9 — Tue, 01 Aug 2017 10:02:38 GMT

Yep, my code works then.

Re: sql join

shivamarrora0 — Tue, 01 Aug 2017 10:03:45 GMT

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

Re: sql join

shivamarrora0 — Tue, 01 Aug 2017 10:05:05 GMT

i am really sorry the desired output i need ,

1 c
2 b
3 d

sorry for the trouble

Re: sql join

RW9 — Tue, 01 Aug 2017 10:14:39 GMT

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;