Tough one: Search through repeated measures then delete.

[kksss]

I'm wrecking my brains on this one. 

I have data in the format of 

        Student ID        Majors                          College

        001                   math                             Science

        001                   Painting                        Fine Art

        002                   Political science           Social Science

        002                   Sociolgy                       Social Science

        003                   math                             Science

        003                   Accounting                    Business

        003                   History                           Humanity

This is a dataset on the college a student's major belong to. E.g Student 002 

So some students will have multiple majors, and on the record it shows they blong to multiple college. E.g Student 001 or student 003

My ultimate purpose is to include/exclude

1) who only belongs to a certain college, no matter how many majors they have, 

2) who has major in one certain college, e.g. science, but also has a major outside that college.  

So my logic is to do a loop search based on their ID #, and then an "If then delete" statement.

But I just can't seem to figure out how to proceed, and need some urgent help

[MelissaM]

I believe I have the first part for you...

DATA OLD;
INPUT STUDENTID $1-3 MAJOR $ 23-40 COLLEGE $ 45-59;
DATALINES;
001                   math                    Science
001                   Painting              Fine Art
002                   Political science     Social Science
002                   Sociolgy              Social Science
003                   math                  Science
003                   Accounting            Business
003                   History               Humanity
;
RUN;

/*who only belongs to a certain college, no matter how many majors they have*/
DATA NEW1;SET OLD;
IF COLLEGE IN ("Science");
RUN;

[mohamed_zaki]

proc sql ;
select distinct student_id from have where student_id in(
select student_id from have group by student_id having count(distinct college)=1) 
and college ='Science';
quit;

proc sql;
select distinct student_id from have where student_id in
(select distinct student_id from have where College='Science') 
 and College ~= 'Science' ;
quit;

[ballardw]

Can you provide an example of how you want the results of the second requirement to appear? There are several ways to do this but the approach could depend on how you want the final data to appear.

[mohamed_zaki]

---

@kksss wrote:

1) who only belongs to a certain college, no matter how many majors they have, 

---

proc sql ;
select *
from have
where student_id in (
select student_id from have where student_id in(
select student_id from have group by student_id having count(distinct college) =1) 
and college ='Science');
quit;

---

@kksss wrote:

2) who has major in one certain college, e.g. science, but also has a major outside that college.  

---

proc sql ;
select *
from have 
where student_id  in 
(select distinct student_id 
 from have 
 where student_id in 
 (select distinct student_id from have where College='Social Science') 
 and College ~= 'Social Science'  );
quit;

[kksss]

(select distinct student_id from have where College='Social Science') 
 and College ~= 'Social Science'  );

Will this actually work?  You have a where condition that is contradiction.

[mohamed_zaki]

Yes it will work. it is not contradiction. Change the college and you will see. This exclude who has double major in the same college but not other college.

[kksss]

let me make it more concrete, as it's a bit complicated

We have a student whoes has three majors Nursing, business, and fine art.

He was registered as a pre-nursing major first, and later converted into an actual nursing major, and then enrolled in second majors, third majors in business and fine art.

however, for whatever glitch we have in the system, it didn't delete his record of being a pre-nursing major, which will belong to University college. (Because if you are a pre-major you are not really in that major college yet).

In our system it show as following

Student ID        Primary Major College              All major list                              Major College

        001                 College of Health                 Nursing, business, fine art                Health

        001                 College of Health                 Nursing, business, fine art               University College

        001                 College of Health                 Nursing, business, fine art               Business

        001                 College of Health                 Nursing, business, fine art               Fine Art

As you can see, the University college shows up because back in time he was a pre-major before. 

So, If I wanna find out students who has a major or all majors ONLY in college of health, this person will be rulled out, because he also belongs to business, and fine art. This is fine with this person. But if Another student has no other majors, but was once a pre-major, it would have shown as 

Student ID        Primary Major College              All major list                              Major College

        001                 College of Health                 Nursing, business, fine art                Health

        001                 College of Health                 Nursing, business, fine art               University College

In this case, I want to still consider him as being college of Health. And I want to return only one row of Student IDs in the system. 

[Reeza]

Do you have dates attached to these records to help sort/identify the latest/timing of the situation?

[kksss]

No, I don't have any dates on them. Gives me headache on this one

[ballardw]

Is the data provided to you in chonological order? If so, then attach an order variable of some sort before attempting to process.

[kksss]

No there isn't it's ordered through A-Z.