Hi All,
Can anyone tell me the code that will find the students who has taken Math 3.
Student_ID Course
101 English 2
101 Math 1
101 Math 3
102 English 1
102 Math 2
102 Math 2
103 Math 2
103 English 1
104 English 1
104 Math 3
Output would be:
Student_ID Course
101 English 2
101 Math 1
101 Math 3
104 English 1
104 Math 3
Thanks,
proc sql;
select *
from table1
where student_id in
(select student_id
from table1
where course="Math 3");
quit;
Hi Reeza,
I tried that and look like I got something, but.....
1. My proc sql is not exporting data in a file, rather it is printing in view.
2. What would be the code if i want to find student who has taken Math 1 and Math 3?
Thanks,
Slight modifications required :
proc sql;
create table WANT as
select *
from table1
where student_id in
(select student_id
from table1
where course = "Math 3")
and student_id in
(select student_id
from table1
where course = "Math 1");
quit;
PG
Basically if you want to know if they took multiple classes you need to make a flag for the taking the class and then choose your students based on that. Also if you want it to show up in a table, put create table _____ as prior to the select statement.
proc sql;
create table want as
select distinct student_id, max(case when course="Math 1" then 1 else 0 end) as math_1, max(case when course="Math 3" then 1 else 0 end) as math_3
from have
group by student_id;
create table want2 as
select distinct student_id
from want
where math_1=1 and math_3 = 1;
quit;
Or equivalently:
proc sql;
create table WANT as
select *
from table1
where student_id in
(select student_id
from table1
where course = "Math 3"
intersect
select student_id
from table1
where course = "Math 1");
quit;
PG
Ah the intersect function in Proc Sql, I hadn't even considered that, learned something new.
thanks PG,
1. What is an intersect? does it works as an AND statement? or OR?
2. What would be the code if I want Math 3 or Math 1?
1) Intersection of two sets includes all elements that are in both sets.
2) Much easier
proc sql;
create table WANT as
select *
from table1
where student_id in
(select student_id
from table1
where course in ("Math 1", "Math 3"));
quit;
PG
Thanks PG, it worked perfect!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.