SAS users,
Using Proc freq, I would like to identify which students received a grade A (where A=1) in year 1, year 2 and year 3 and I would like to sort it by school.
Here's a sample of my code:
proc sort data=have;
by school;
proc freq data=have;
tables year;
where grade=1;
by school;
run;
However I don't know how to specify that the student must have an A (grade=1) in all years, not just one.
Thanks.
If GRADE=1 means a grade of A your tables should only be looking grades of A regardless of year.
You might want to use TABLE SCHOOL*YEAR / LIST; if you want a single table sorted by school. HINT: Freq display the output in order without a separate sort using the formatted values of a variable.
If you don't think the where is working you can test your code by putting the grade variable in the table statement.
TABLE SCHOOL*YEAR*GRADE / LIST;
Thank you. However I'm not sure how I an see which students received an A in all three years. I would like to track individual students rather than the overall percentage of students who received an A. (I.e. who received an A in both year 1 and year 2). Maybe I'm missing something?
you can create a dataset with students who got As in all three years.
data have;
input id school year grade;
cards;
1 1 1 1
1 1 2 2
1 1 3 1
2 1 1 1
2 1 2 1
2 1 3 1
1 2 1 1
1 2 2 2
1 2 3 1
5 2 1 1
5 2 2 1
5 2 3 1
;
proc sql;
create table want as select *, sum(grade) as total
from have
group by school,id
having total=3
order by school;
quit;
proc print;run;
hi ... this makes some fake data with variables SCHOOL, STUDENT, YEAR, and GRADE then uses SQL to find students within schools with A in all three years (RANTBL ... 30% chance that student gets an A in any given year) ...
data have;
do year = 2008 to 2010;
do school = 1 to 5;
do student = 1 to 100;
grade = rantbl(999,0.3,0.5);
output;
end; end; end;
run;
proc sql;
create table gradea as
select school, student from have (where=(grade eq 1))
group by school, student
having count(*) eq 3
order school, student;
quit;
school=1
student
32
33
35
87
92
school=2
student
52
school=3
student
16
62
school=4
student
4
19
58
75
Hi Mike,
how did "rantbl(999,0.3,0.5)" assign value 1,2,3 to grade?
Thanks - Linlin
Hi ... 1st argument is a seed, remaining arguments assign 1, 2, 3, 4, etc with the probability you specify ...
a/ probabilities do not sum to 1, RANTBL sums those specified and subtracts them from 1 to get one more assignment rule
b/ probability sum > 1, RANTBL adjusts/ignores stated probabilities
data test;
do _n_ = 1 to 1e5;
x = rantbl(0, 0.3, 0.4, 0.25);
y = rantbl(0, 0.3, 0.4, 0.25, 0.05);
z = rantbl(0, 0.3, 0.4, 0.35, 0.35);
output;
end;
run;
Cumulative Cumulative
x Frequency Percent Frequency Percent
1 30118 30.12 30118 30.12
2 39952 39.95 70070 70.07
3 24898 24.90 94968 94.97
4 5032 5.03 100000 100.00
Cumulative Cumulative
y Frequency Percent Frequency Percent
1 30010 30.01 30010 30.01
2 39996 40.00 70006 70.01
3 25022 25.02 95028 95.03
4 4972 4.97 100000 100.00
Cumulative Cumulative
z Frequency Percent Frequency Percent
1 30136 30.14 30136 30.14
2 39908 39.91 70044 70.04
3 29956 29.96 100000 100.00
Another use is to randomly assign a value from an array to a variable ...
array names(5) $6 _temporary_ ('mike' 'linlin' 'ksharp' 'art' 'peterc');
name = names(rantbl(0, 0.2, 0.2, 0.2, 0.2));
Cumulative Cumulative
name Frequency Percent Frequency Percent
art 19869 19.87 19869 19.87
ksharp 19903 19.90 39772 39.77
linlin 20122 20.12 59894 59.89
mike 20089 20.09 79983 79.98
peterc 20017 20.02 100000 100.00
FYI ... there's also CALL RANTBL, and from online doc ...
The CALL RANTBL routine gives greater control of the seed and random number streams than does the RANTBL function.
Thank you Mike! I understand now.
if we change "grade = rantbl(999,0.3,0.5);" to
"grade = rantbl(999,0.3,0.7);" then grade would be 1,2.
to
"grade = rantbl(999,0.3,0.4,0.2);" then grade would be 1,2,3,4.
Thanks - Linlin
Assuming You have no replicated observations .
data have; input id school year grade; cards; 1 1 1 1 1 1 2 2 1 1 3 1 2 1 1 1 2 1 2 1 2 1 3 1 1 2 1 1 1 2 2 2 1 2 3 1 5 2 1 1 5 2 2 1 5 2 3 1 ; run; proc sort data=have;by school; run; proc freq data=have noprint; where grade=1; tables school*id/out=want(drop=percent where=(count=3)) nocum nopercent; run;
Ksharp
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.