topic Re: How to retain all observations in a dataset when using PROC SQL to combine two datasets? in SAS Programming

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

tediest — Fri, 28 Sep 2012 01:22:07 GMT

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!

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

Linlin — Fri, 28 Sep 2012 01:38:37 GMT

using left join.

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

PGStats — Fri, 28 Sep 2012 01:40:48 GMT

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;

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

tediest — Fri, 28 Sep 2012 01:44:53 GMT

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

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

tediest — Fri, 28 Sep 2012 01:45:13 GMT

Thanks PG! This is exactly what I need.