Selecting only certain observations

Accepted Solution Solved
Reply
Regular Contributor
Posts: 150
Accepted Solution

Selecting only certain observations

I have 600 participants in a dataset but only want to select certain ID numbers to subset the data. What is the best way to do this?

I thought this would work but it does not. The ID variable is a character variable

data baseline5;

set baseline;

if pid ^=("0107" or "0347" or "0106" or "0494" or "0496" or "0044" or "0340" or "0120" or "0499" or "0158" or "0348" or

"0353" or  "0507" or "0497" or "0370" or "0371" or "0372" or "0509" or "0360" or "0510"

or "0364" or "0511" or "0165" or "0512" or  "0179" or "0170" or "0171" or "0172" or "0514") then delete;

run;


Accepted Solutions
Solution
‎11-06-2013 03:41 PM
Super User
Posts: 17,731

Re: Selecting only certain observations

You need not in.

if PID NOT IN ("0107" "0347" )  ;

Alternatively if the data is in a dataset:

proc sql;

create table want as

select * from have

where pid not in (select ID from table_with_ids);

quit;

View solution in original post


All Replies
Solution
‎11-06-2013 03:41 PM
Super User
Posts: 17,731

Re: Selecting only certain observations

You need not in.

if PID NOT IN ("0107" "0347" )  ;

Alternatively if the data is in a dataset:

proc sql;

create table want as

select * from have

where pid not in (select ID from table_with_ids);

quit;

Regular Contributor
Posts: 150

Re: Selecting only certain observations

This seems correct but I am a little confused. When I run this:

proc sql;

create table baseline5 as

select * from baseline

where pid not in (select "0107" "0347" "0106" "0494" "0496" "0044" "0340"  "0120"  "0499"  "0158" "0348"

"0353"   "0507"  "0497" "0370"  "0371" "0372" "0509"  "0360"  "0510"

"0364"  "0511"  "0165"  "0512" "0179" "0170"  "0171"  "0172"  "0514" from baseline);

quit;

it just gives me the whole dataset...What do I need to modify. Thank you for your help.

Super User
Posts: 17,731

Re: Selecting only certain observations

Those were two different answers, don't mix them. The following should work.

proc sql;

create table baseline5 as

select * from baseline

where pid not in ( "0107" "0347" "0106" "0494" "0496" "0044" "0340"  "0120"  "0499"  "0158" "0348"

"0353"   "0507"  "0497" "0370"  "0371" "0372" "0509"  "0360"  "0510"

"0364"  "0511"  "0165"  "0512" "0179" "0170"  "0171"  "0172"  "0514" );

quit;

Regular Contributor
Posts: 150

Re: Selecting only certain observations

I actually tried that first and it deletes those PID numbers and I want it to keep them. Is there a way for it to keep them instead of delete them?

Respected Advisor
Posts: 3,886

Re: Selecting only certain observations

What do you think is the difference between "not in" and "in"?

Regular Contributor
Posts: 150

Re: Selecting only certain observations

Of course I know what the difference is but am not familiar with this procedure so I did not realize it was that simple. Thank you!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 241 views
  • 0 likes
  • 3 in conversation