topic Re: SQL Table with distinct counts in SAS Programming

SQL Table with distinct counts

ReiYama — Sat, 27 Feb 2021 21:41:06 GMT

I have some data

Year	Student ID	Class
2000	1	Math
2000	2	Math
2000	3	English
2000	4	English
2001	1	Math
2001	2	Chemistry
2001	3	English
2001	1	Chemistry

And want to create a table for the number of distinct students and distinct classes. i.e.

Year	Student ID	Class
2000	4	2
2001	3	3

However, my code:

proc sql;
	create table a.registry as select distinct year as year, count(distinct i.student) as student, count(distinct i.class) as class
	from a.list i;
quit;

gives me the same value each year, i.e.

Year	Student ID	Class
2000	4	3
2001	4	3

Does anyone know how to fix this?

Re: SQL Table with distinct counts

novinosrin — Sat, 27 Feb 2021 21:46:01 GMT



data have;
input Year Student_ID	Class :$15.;
cards;
2000	1	Math
2000	2	Math
2000	3	English
2000	4	English
2001	1	Math
2001	2	Chemistry
2001	3	English
2001	1	Chemistry
;

proc sql;
 create table want as
 select year, count(distinct Student_ID) as Student_ID,
 count(distinct class) as class
 from have
 group by year;
quit;

Year	Student_ID	class
2000	4	2
2001	3	3

Re: SQL Table with distinct counts

ReiYama — Sun, 28 Feb 2021 14:58:59 GMT

Thank you! This worked perfectly!