SQL query: use the results from one query in a separate query

[gzr2mz39]

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?

Thank you.

[Reeza]

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.

Base SAS(R) 9.2 Procedures Guide

See the sections on outer and inner joins which also go over left/right joins.

[gzr2mz39]

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.

For example,

data bird_sub;

set bird1;

where sys_id in (1,2,5,9);

run;

I would like to create a SQL block of code that does this.

Thank you.

[Reeza]

Something like the following perhaps?

proc sql;

create table want as

select *

from have

where sys_id in (select id from bird_all where year=2014 and primary_grp=1);

quit;

[gzr2mz39]

Thank you.