topic Re: Inner join with multiple condition? in SAS Programming

Inner join with multiple condition?

Phil_NZ — Fri, 12 Feb 2021 20:42:36 GMT

Hi SAS Users,

Today I tried to inner join two datasets "winsed.merge_treat_con" and "_9b_lag" based on two variables "Year" and "Type" (both are character variables)

In the joined dataset, I want to have all variables of the winsed.merge_treat_con and these variables del_exc_rat, lagcogs, lagsel_gen_adm, lagope_lea_exp, lagr_and_d, laggoodwill from _9b_lag.

My code based on one condition is as below

PROC SQL;
Create table innerjoin_ as
Select * from winsed.merge_treat_con as x, del_exc_rat lagcogs lagsel_gen_adm lagope_lea_exp 
		lagr_and_d laggoodwill from _9b_lag	as y
where x.Type = y.Type;
Quit;

I am not sure how can I deal with two conditions and apart from Proc SQL, and whether we can use other ways to merge inner join like that (because sometimes SQL consumes quite a bit of ram).

Many thanks and warm regards.

Re: Inner join with multiple condition?

utrocketeng — Fri, 12 Feb 2021 20:57:45 GMT

it sounds this might be what you are after

PROC SQL;
Create table innerjoin_ as
Select 
	x.*
	, del_exc_rat 
	, lagcogs 
	, lagsel_gen_adm 
	, lagope_lea_exp 
	, lagr_and_d 
	, laggoodwill
from 
	winsed.merge_treat_con as x
	INNER JOIN _9b_lag	as y ON x.Type = y.Type;
Quit;

Re: Inner join with multiple condition?

PGStats — Fri, 12 Feb 2021 21:07:26 GMT

... or if you need two conditions:

.... ON x.Type = y.Type AND x.Year = y.Year

Re: Inner join with multiple condition?

Shmuel — Fri, 12 Feb 2021 21:09:51 GMT

There are many syntax errors in your sql code.

Compare it to next code:

PROC SQL;
	Create table innerjoin_ as
	Select x.*,
	       y.del_exc_rat,
		   y.lagcogs,
		   y.lagsel_gen_adm,
		   y.lagope_lea_exp, 
		   y.lagr_and_d,
		   y.laggoodwill
	from winsed.merge_treat_con as x
	inner join 	work._9blag	as y	
	on x.year=y.year and x.Type = y.Type;
Quit;