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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.