BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KonstantinVasil
Obsidian | Level 7

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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

2 REPLIES 2
s_lassen
Meteorite | Level 14

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;
Satish_Parida
Lapis Lazuli | Level 10
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 8252 views
  • 13 likes
  • 3 in conversation