How to retain all observations in a dataset when using PROC SQL to combine two datasets?

[tediest]

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!

[Linlin]

using left join.

[tediest]

Thanks a lot for the prompt reply! This is very helpful. I have been searching for the right command but missed "left join".

[PGStats · Accepted solution]

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

[tediest]

Thanks PG! This is exactly what I need.