topic Re: Tough one: Search through repeated measures then delete. in SAS Procedures

Tough one: Search through repeated measures then delete.

kksss — Fri, 05 Feb 2016 21:39:17 GMT

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

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

MelissaM — Fri, 05 Feb 2016 22:18:50 GMT

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;

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

ballardw — Fri, 05 Feb 2016 22:44:37 GMT

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.

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

mohamed_zaki — Sat, 06 Feb 2016 01:29:37 GMT

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

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

kksss — Fri, 05 Feb 2016 22:59:52 GMT

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.

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

kksss — Fri, 05 Feb 2016 23:02:03 GMT

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

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

mohamed_zaki — Fri, 05 Feb 2016 23:06:39 GMT

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.

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

Reeza — Sat, 06 Feb 2016 00:02:00 GMT

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

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

mohamed_zaki — Sat, 06 Feb 2016 01:50:10 GMT

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;

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

kksss — Mon, 08 Feb 2016 14:37:48 GMT

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

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

ballardw — Mon, 08 Feb 2016 17:09:27 GMT

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

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

kksss — Mon, 08 Feb 2016 20:31:10 GMT

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