Calcite | Level 5

## Tough one: Search through repeated measures then delete.

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

11 REPLIES 11
Obsidian | Level 7

## Re: Tough one: Search through repeated measures then delete.

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                   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;

Barite | Level 11

## Re: Tough one: Search through repeated measures then delete.

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

## Re: Tough one: Search through repeated measures then delete.

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.

Barite | Level 11

## Re: Tough one: Search through repeated measures then delete.

@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;

Calcite | Level 5

## Re: Tough one: Search through repeated measures then delete.

(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.

Barite | Level 11

## Re: Tough one: Search through repeated measures then delete.

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.

Calcite | Level 5

## Re: Tough one: Search through repeated measures then delete.

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               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.

Super User

## Re: Tough one: Search through repeated measures then delete.

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

Calcite | Level 5

## Re: Tough one: Search through repeated measures then delete.

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

Super User

## Re: Tough one: Search through repeated measures then delete.

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

Calcite | Level 5

## Re: Tough one: Search through repeated measures then delete.

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

Discussion stats
• 11 replies
• 1229 views
• 0 likes
• 5 in conversation