DATA Step, Macro, Functions and more

Proc Sql + Left Join - Overwrite values of existing variables

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Proc Sql + Left Join - Overwrite values of existing variables

 

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


Accepted Solutions
Highlighted
Solution
‎02-01-2018 08:10 AM
PROC Star
Posts: 253

Re: Proc Sql + Left Join - Overwrite values of existing variables

Posted in reply to KonstantinVasil

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;

View solution in original post


All Replies
Highlighted
Solution
‎02-01-2018 08:10 AM
PROC Star
Posts: 253

Re: Proc Sql + Left Join - Overwrite values of existing variables

Posted in reply to KonstantinVasil

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;
Frequent Contributor
Posts: 109

Re: Proc Sql + Left Join - Overwrite values of existing variables

[ Edited ]
Posted in reply to KonstantinVasil
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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 538 views
  • 12 likes
  • 3 in conversation