topic Re: proc freq and where statements in SAS Procedures

proc freq and where statements

sophia_SAS — Fri, 27 Apr 2012 16:58:31 GMT

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.

Re: proc freq and where statements

ballardw — Fri, 27 Apr 2012 17:10:41 GMT

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;

Re: proc freq and where statements

sophia_SAS — Fri, 27 Apr 2012 17:32:43 GMT

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?

Re: proc freq and where statements

Linlin — Fri, 27 Apr 2012 17:34:21 GMT

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;

Re: proc freq and where statements

MikeZdeb — Fri, 27 Apr 2012 17:40:05 GMT

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

school=2

student

school=3

student

school=4

student

Re: proc freq and where statements

Ksharp — Sat, 28 Apr 2012 03:22:49 GMT

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

Re: proc freq and where statements

Linlin — Sat, 28 Apr 2012 13:11:32 GMT

Hi Mike,

how did "rantbl(999,0.3,0.5)" assign value 1,2,3 to grade?

Thanks - Linlin

Re: proc freq and where statements

MikeZdeb — Sat, 28 Apr 2012 17:23:35 GMT

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.

Re: proc freq and where statements

Linlin — Sat, 28 Apr 2012 19:09:56 GMT

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.

"grade = rantbl(999,0.3,0.4,0.2);" then grade would be 1,2,3,4.

Thanks - Linlin