BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Debora1
Obsidian | Level 7

Hello!

I am using SQL trying to view the nct_id column that matches 3  criteria and i have 2 tables that share the nct_id.

  1. 2 criterias are within the table studies
  2. Last criteria is in table interventions.

I am not sure how to connect first two criterias to the inner join, do you have any clue?

select studies.nct_id 
	from studies where study_type='Interventional' and overall_status='Completed'
	inner join interventions 
		on studies.nct_id=interventions.nct_id where intervention_type='Drug';	

ERROR: syntax error at or near "inner" LINE 20: inner join interventions on studies.nct_id=interventions.n... ^

 

Many thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
sustagens
Pyrite | Level 9

You only need one "where" after your "from". You can specify what table to apply your filter by using its alias (or table name in your code)

Try this

 

select studies.nct_id
from studies 
inner join interventions on studies.nct_id=interventions.nct_id where studies.study_type = 'Interventional' and studies.overall_status = 'Completed' and intervention.intervention_type = 'Drug'

 

View solution in original post

6 REPLIES 6
sustagens
Pyrite | Level 9

You only need one "where" after your "from". You can specify what table to apply your filter by using its alias (or table name in your code)

Try this

 

select studies.nct_id
from studies 
inner join interventions on studies.nct_id=interventions.nct_id where studies.study_type = 'Interventional' and studies.overall_status = 'Completed' and intervention.intervention_type = 'Drug'

 

Debora1
Obsidian | Level 7
Thaaaanks so much! Thaaat now makes a lot of sense!
ChrisNZ
Tourmaline | Level 20

Or, if you want use SAS data set options:

select studies.nct_id 
  from studies(where=(study_type='Interventional' and overall_status='Completed'))
         inner join
       interventions (where=(intervention_type='Drug'))
	     on studies.nct_id = interventions.nct_id 

 

Debora1
Obsidian | Level 7
Thanks so much Chris! Thats a lot simpler than what i was doing!
Kurt_Bremser
Super User
proc sql;
create table want as
  select studies.nct_id 
  from studies st, interventions in
  where
    st.nct_id = in.nct_id
    and st.study_type = 'Interventional' and st.overall_status = 'Completed'
	and in.intervention_type = 'Drug'
;	
quit;
Debora1
Obsidian | Level 7
Hello Kurt!
Thanks, that was also something I was looking to do

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6 replies
  • 655 views
  • 4 likes
  • 4 in conversation