02-20-2014 07:59 PM
I would like to subset a dataset (i.e. bird1) using the IDs (i.e. sys_id) that are returned from the query of a different dataset (i.e. bird_all).
So, I would like to create a new dataset (i.e. bird_sub which is a subset of bird1) that only contains the IDs that matched the following criteria in bird_all: year=2014 and primary_grp=1.
Can anyone show me the SQL code for this?
02-20-2014 08:13 PM
I couldn't follow that. You have three datasets? bird1, bird_all, and bird_sub? bird_sub is a subset of bird1, but bird1 is also a subset of bird_all?
You can join with the left or right options, which then keep on the data on the corresponding side of the query.
See the sections on outer and inner joins which also go over left/right joins.
02-20-2014 08:25 PM
I have two datasets and I want to create a new a dataset.
The new dataset (i.e. bird_sub) will be a subset of bird1.
bird1 will be subset using values of sys_id.
These values need to be passed from bird_all using the following criteria: year=2014 and primary_grp=1.
What I don't want to do is proc print all the values of sys_id from bird_all that meet the criteria and then copy and paste these values into a where statement.
where sys_id in (1,2,5,9);
I would like to create a SQL block of code that does this.
02-20-2014 08:58 PM
Something like the following perhaps?
create table want as
where sys_id in (select id from bird_all where year=2014 and primary_grp=1);