BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASdevAnneMarie
Barite | Level 11

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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
Barite | Level 11
Thank you, Tom

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 365 views
  • 1 like
  • 2 in conversation