topic data merge in SAS Programming

Data merge

kajal_30 — Sun, 10 Feb 2019 00:49:22 GMT

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

Re: Data merge

Reeza — Sun, 10 Feb 2019 00:55:42 GMT

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

data merge

kajal_30 — Sun, 10 Feb 2019 07:46:29 GMT

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

Re: data merge

PeterClemmensen — Sun, 10 Feb 2019 08:37:04 GMT

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

Re: data merge

VRKiwi — Sun, 10 Feb 2019 09:13:13 GMT

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

Re: data merge

Patrick — Sun, 10 Feb 2019 09:20:52 GMT

@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

Re: data merge

VRKiwi — Sun, 10 Feb 2019 09:29:14 GMT

@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.

Re: Data merge

Kurt_Bremser — Sun, 10 Feb 2019 09:34:01 GMT

I had to merge your identical questions.

PLEASE DO NOT DOUBLE-POST.