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.
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!
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'
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'
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.