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?
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 |
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 |
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.