BookmarkSubscribeRSS Feed
mlogan
Lapis Lazuli | Level 10

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,

9 REPLIES 9
Reeza
Super User

proc sql;

select *

from table1

where student_id in

     (select student_id

         from table1

         where course="Math 3");

quit;

mlogan
Lapis Lazuli | Level 10

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,

PGStats
Opal | Level 21

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

PG
overmar
Obsidian | Level 7

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;

PGStats
Opal | Level 21

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

PG
overmar
Obsidian | Level 7

Ah the intersect function in Proc Sql, I hadn't even considered that, learned something new.

mlogan
Lapis Lazuli | Level 10

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?

PGStats
Opal | Level 21

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

PG
mlogan
Lapis Lazuli | Level 10

Thanks PG, it worked perfect!!!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to choose a machine learning algorithm

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.

Discussion stats
  • 9 replies
  • 1464 views
  • 2 likes
  • 4 in conversation