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.
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.
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.