Data merge

[kajal_30]

data merge (drop = w);

merge x (in = a rename = ( new = old)) 

y (in= b) ;

by t ;

if a and b then d = "e" ;

if a and not b then d = "f" ;

if b and not a then d = "g" ;

run;

I need help converting this into proc sql

thanks

[Reeza]

Switch the IF/THEN statements to CASE statements. If you're having difficulties, post the code you've tried.

This assumes a one to one or one to many join, not a many to many join. If it's a many to many join, you cannot replicate it via SQL.

---

@kajal_30 wrote:

data merge (drop = w);

merge x (in = a rename = ( new = old)) 

y (in= b) ;

by t ;

if a and b then d = "e" ;

if a and not b then d = "f" ;

if b and not a then d = "g" ;

run;

I need help converting this into proc sql

thanks

---

[kajal_30]

data merge (drop = w);

merge x (in = a rename = ( new = old)) 

y (in= b) ;

by t ;

if a and b then d = "e" ;

if a and not b then d = "f" ;

if b and not a then d = "g" ;

run;

I need help converting this into proc sql under case when how to handle in = a and in = b

thanks

[PeterClemmensen]

Why do you want to do this in SQL? The data step seems to be a fine tool to me?

[VRKiwi]

Something like this?

data X ;
input T NEW $;
cards;
1 a
2 b
run;
data Y;
input T NEW $;
cards;
1 c
3 d
run;

proc sql;
  create table WANT as 
    select X.NEW as OLD
         , Y.NEW
         , coalesce (X.T, Y.T) as T
         , case when X.T is null then "g"
                when Y.T is null then "f"
                else                  "e" end as D             
    from WORK.X 
           full outer join
         WORK.Y
           on X.T = Y.T;     
quit; 
    

OLD	NEW	T	D
a	c	1	e
b		2	f
	d	3	g

[Patrick]

@kajal_30 

Using a SQL Full Join you will only get the same result than with the merge if your variable T is the key in the tables and the relationship between the tables is not many:many.

You can use a CASE statement to work out which table contributes to the result set. If a table doesn't contribute then the value of t from this table will be missing.

   select 
      ....
      x.new as old,
      case
        when x.t=y.t then 'e'
        when missing(y.t) then 'f'
        when missing*x.t) then 'g'
        else ' '
        end as d,
      ....
    from x full join y
      on x.t=y.t

[VRKiwi]

---

@Patrick wrote:

Using a SQL Full Join you will only get the same result than with the merge if your variable T is the key in the tables and the relationship between the tables is not many:many.

---

... and also if the key (T here) doesn't have a missing value in any table.

[Kurt_Bremser]

I had to merge your identical questions.

PLEASE DO NOT DOUBLE-POST.