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!!!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.