Solved
Contributor
Posts: 20

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

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

Accepted Solutions
Solution
‎09-27-2012 09:40 PM
Posts: 5,526

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

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

PG

All Replies
Super Contributor
Posts: 1,636

using left join.

Contributor
Posts: 20

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

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

Solution
‎09-27-2012 09:40 PM
Posts: 5,526

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

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

PG
Contributor
Posts: 20

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

Thanks PG! This is exactly what I need.

🔒 This topic is solved and locked.