topic Re: Occurances within by group in SAS Data Science

Occurances within by group

mlogan — Wed, 27 May 2015 18:44:39 GMT

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

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,

Re: Occurances within by group

Reeza — Wed, 27 May 2015 19:05:59 GMT

proc sql;

select *

from table1

where student_id in

(select student_id

from table1

where course="Math 3");

quit;

Re: Occurances within by group

mlogan — Wed, 27 May 2015 19:26:45 GMT

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,

Re: Occurances within by group

PGStats — Wed, 27 May 2015 19:42:46 GMT

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;

Re: Occurances within by group

overmar — Wed, 27 May 2015 19:47:36 GMT

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;

Re: Occurances within by group

PGStats — Wed, 27 May 2015 19:49:09 GMT

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;

Re: Occurances within by group

overmar — Wed, 27 May 2015 19:51:12 GMT

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

Re: Occurances within by group

mlogan — Wed, 27 May 2015 20:02:52 GMT

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?

Re: Occurances within by group

PGStats — Wed, 27 May 2015 20:14:53 GMT

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;

Re: Occurances within by group

mlogan — Wed, 27 May 2015 20:19:10 GMT

Thanks PG, it worked perfect!!!