BookmarkSubscribeRSS Feed
gzr2mz39
Quartz | Level 8

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.

4 REPLIES 4
Reeza
Super User

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
Quartz | Level 8

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 780 views
  • 1 like
  • 2 in conversation