proc sql : inner and left join

[SASdevAnneMarie]

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 !

[Tom · Accepted solution]

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.

[SASdevAnneMarie]

Thank you, Tom