topic Re: proc sql : inner and left join in SAS Programming

proc sql : inner and left join

SASdevAnneMarie — Mon, 11 Dec 2023 21:45:42 GMT

Hello Experts,

My code is :

proc sql;
	create table test as select distinct
		ev0_2.POLICE,
		ev0_2.SUPPORT,
		ev0_2.SUPPORT2,
		ev1.EFFET as EF
	from 
		(select ev0.NO_POLICE, sup.SUPPORT,sup.SUPPORT2
			from  EVEN ev0 inner join SUPPORT sup on  ev0.SUPPORT=sup.SUPPORT ) ev0_2
				left join EV ev1 on ev0_2.POLICE=ev1.NO_POLICE and ev0_2.SUPPORT=ev1.SUPPORT;
quit;

Firstly, I would like to check the common observation from the table EVEN and SUPPORT and after I would like the observation with left join. Could you please check if my code is right beacause my programme is looping.

Thank you !

Re: proc sql : inner and left join

Tom — Mon, 11 Dec 2023 22:15:12 GMT

You are referencing a non-existent variable.

In the sub-query you select ev0.NO_POLICE but in the outer query you are looking for a variable named POLICE without the NO_ prefix.

How many values of SUPPORT are there in the three datasets?

How many observations per value of SUPPORT?

If for a single value of SUPPORT you join N observations from EVO with M observation from SUP you will get N*M observation. If you then join that with Q observations for that value of SUPPORT from EV1 you will end up with N*M*Q observation. Which could be a really big number.

Re: proc sql : inner and left join

SASdevAnneMarie — Sat, 16 Dec 2023 10:58:52 GMT

Thank you, Tom