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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.