DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

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

Thanks a lot in advance!


Accepted Solutions
Solution
‎09-27-2012 09:40 PM
Respected Advisor
Posts: 4,644

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

View solution in original post


All Replies
Super Contributor
Posts: 1,636

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

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
Respected Advisor
Posts: 4,644

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 260 views
  • 3 likes
  • 3 in conversation