I found many threads on this question with solutions using merge. I understand why proc sql is doing what it does but I want to know if there is elegant way to left join with proc sql and for the rows with matching keys overwrite the value of a variable in the left table with the one from the right (the variable exists in both tables with the same name).
Example:
data test1; input id age ; datalines; 1 25 2 26 3 27 ; data test2; input id age ; datalines; 2 100 ; proc sql; create table test3 as select a.*, b.age from test1 a left join test2 b on a.id=b.id; quit;
The resulting output is:
id age
1 25
2 26
3 27
The desired output is:
1 25
2 100
3 27
I think the COALESCE function does what you want:
proc sql;
create table test3 as
select
test1.id,
coalesce(test2.age,test1.age) as age
from test1
left join test2
on test1.id=test2.id;
quit;
I think the COALESCE function does what you want:
proc sql;
create table test3 as
select
test1.id,
coalesce(test2.age,test1.age) as age
from test1
left join test2
on test1.id=test2.id;
quit;
data test1;
input id age ;
datalines;
1 25
2 26
3 27
;
data test2;
input id age ;
datalines;
2 100
;
proc sql;
create table test3 as
select a.id,ifn(b.age ne ., b.age, a.age) as age
from test1 a
left join test2 b
on a.id=b.id;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.