topic Re: SQL query: use the results from one query in a separate query in SAS Procedures

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

gzr2mz39 — Fri, 21 Feb 2014 00:59:20 GMT

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.

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

Reeza — Fri, 21 Feb 2014 01:13:32 GMT

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.

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

gzr2mz39 — Fri, 21 Feb 2014 01:25:42 GMT

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.

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

Reeza — Fri, 21 Feb 2014 01:58:16 GMT

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;

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

gzr2mz39 — Fri, 21 Feb 2014 17:25:29 GMT

Thank you.