I need to use proc sql to select information from one data set and match to another.
This is what I did:
proc sql;
create table merge as
select * from data1 as a, data2 as b
where a.var1=b.var1 and (a.var2> b.var4) and (a.var3<= b.var4);
quit;
However, after this, I would lose observations in data1 that do not satisfy the conditions "a.var1=b.var1 and (a.var2> b.var2) and (a.var2<= b.var2)“. Is there a way to retain all information in a and simply set the value of var3 to 0?
pro
Thanks a lot in advance!
I guess it's var4 that you want to set to 0 when there is no match in data2? To do that, you can use a left join and the coalesce function, like this :
proc sql;
create table merge as
select a.*, coalesce(b.var4,0) as var4
from data1 as a left join data2 as b
on a.var1=b.var1 and (a.var2> b.var4) and (a.var3<= b.var4);
quit;
PG
using left join.
Thanks a lot for the prompt reply! This is very helpful. I have been searching for the right command but missed "left join".
I guess it's var4 that you want to set to 0 when there is no match in data2? To do that, you can use a left join and the coalesce function, like this :
proc sql;
create table merge as
select a.*, coalesce(b.var4,0) as var4
from data1 as a left join data2 as b
on a.var1=b.var1 and (a.var2> b.var4) and (a.var3<= b.var4);
quit;
PG
Thanks PG! This is exactly what I need.
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.