## proc freq and where statements

Frequent Contributor
Posts: 114

# proc freq and where statements

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.

Super User
Posts: 13,583

## Re: proc freq and where statements

Posted in reply to sophia_SAS

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;

Frequent Contributor
Posts: 114

## Re: proc freq and where statements

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?

Super Contributor
Posts: 1,636

## Re: proc freq and where statements

Posted in reply to sophia_SAS

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;

Valued Guide
Posts: 765

## Re: proc freq and where statements

Posted in reply to sophia_SAS

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

Super Contributor
Posts: 1,636

## Re: proc freq and where statements

Hi Mike,

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

Thanks - Linlin

Valued Guide
Posts: 765

## Re: proc freq and where statements

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.

Super Contributor
Posts: 1,636

## Re: proc freq and where statements

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

Super User
Posts: 10,787

## Re: proc freq and where statements

Posted in reply to sophia_SAS

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

Discussion stats
• 8 replies
• 417 views
• 2 likes
• 5 in conversation