BookmarkSubscribeRSS Feed
kksss
Calcite | Level 5

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

 

11 REPLIES 11
MelissaM
Obsidian | Level 7

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
Barite | Level 11
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
Super User

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
Barite | Level 11

@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
Calcite | Level 5
(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
Barite | Level 11

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
Calcite | Level 5

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

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

kksss
Calcite | Level 5

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

ballardw
Super User

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

kksss
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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