turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- proc freq and where statements

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2012 12:58 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2012 01:10 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2012 01:32 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2012 01:34 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2012 01:40 PM

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**

s**tudent**

** 52**

**school=3**

s**tudent**

** 16**

** 62**

**school=4**

**student**

** 4**

** 19**

** 58**

** 75**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-28-2012 09:11 AM

Hi Mike,

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

**Thanks - Linlin**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-28-2012 01:23 PM

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. **

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-28-2012 03:09 PM

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**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2012 11:22 PM

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