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
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 (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
Why do you want to do this in SQL? The data step seems to be a fine tool to me?
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 |
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
@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.
I had to merge your identical questions.
PLEASE DO NOT DOUBLE-POST.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.