Finding the observations whose variable's values are the same as a specific observation

[m1986MM]

Hi everyone,

I want to create a subset from a dataset. Suppose I have a table like below. What I want to do is keeping all those observations with the same value of Class_ID as what student 1111 has. In this case, I want to create a subset of all observations that the values of their class_ID are equal to 22 or 33.

Student_ID	Class_ID	Score
1111	22	A
1111	33	A
2520	22	A
2520	44	A
5148	33	A
5148	66	A
6251	55	A

I appreciate any suggestion.

[ballardw]

data want;

     set have;

     where Class_id in (22,23);

run;

If Class_id is character then use ('22','23')

[m1986MM]

Actually the dataset is very large. So I can't easily find all values, like 22 or 33. I'm looking for an algorithm that can find all the values belonging to a certain student_ID, and then keep those observations that have the same value.

[Reeza · Accepted solution]

This won't scale to larger data, you need to be more explicit to what your problem is.

proc sql;

create table want as

select *

from have

where class_id in (select class_id from have where student_id = '1111');

quit;

[SelsonMuniappen]

Without further detail about what you are trying to do, my best suggestion would be the same as Reeza, assuming that for every case you know which student_id has the values you are looking for.