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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: