topic Proc Sql + Left Join - Overwrite values of existing variables in SAS Programming

Proc Sql + Left Join - Overwrite values of existing variables

KonstantinVasil — Thu, 01 Feb 2018 12:31:13 GMT

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

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

s_lassen — Thu, 01 Feb 2018 12:44:01 GMT

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;

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

Satish_Parida — Thu, 01 Feb 2018 12:51:51 GMT

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;