Help using Base SAS procedures

sql join

Reply
Contributor
Posts: 39

sql join

Hi All,

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

 

Capture.PNG

Trusted Advisor
Posts: 1,564

Re: sql join

Posted in reply to shivamarrora0

It seems that table b updates table a:

 

data nn;

 update a b;

   by id;

run;

PROC Star
Posts: 742

Re: sql join

Posted in reply to shivamarrora0

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

Super User
Super User
Posts: 7,955

Re: sql join

Posted in reply to shivamarrora0

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;

 

Contributor
Posts: 39

Re: sql join


data a;
input id name$;
cards;
1 a
2 b
;
data b;
input id name$;
cards;
1 c
3 d
;
Super User
Super User
Posts: 7,955

Re: sql join

Posted in reply to shivamarrora0

Yep, my code works then.

Contributor
Posts: 39

Re: sql join

i am really sorry the desired output i need ,

1 c
2 b
3 d



sorry for the trouble
Super User
Super User
Posts: 7,955

Re: sql join

Posted in reply to shivamarrora0

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;
Super User
Posts: 7,782

Re: sql join

Posted in reply to shivamarrora0

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 39

Re: sql join

Posted in reply to shivamarrora0
I am really sorry Folks, the desired output is 1 c 2 b 3 d
Ask a Question
Discussion stats
  • 9 replies
  • 233 views
  • 1 like
  • 5 in conversation