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
- /
- Base SAS Programming
- /
- Calculating Individual Percentiles By Teacher and ...

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

11-03-2016 02:05 PM

I'd like to automate my SAS code to calculate what percentile (Q1,Q2,Q3,Q4) my teachers spent in the classroom by school year.

My dataset looks like this:

teacher school_year hours_spent_in_classroom

A 2012 350

A 2013 150

B 2014 250

C 2014 350

ALL_TEACHERS 2012 600

I can use proc univariate to know what percentiles are for each year. I could hard code those percentile values into my dataset, and then do "if hours_spent_in_classroom GE X then quartile = 1". But this is a lot of manual work, and it would change by school_year.

I'd like to have a final dataset that looks like this:

teacher school_year hours_spent_in_classroom Quartile

A 2012 350 1

A 2013 150 2

B 2014 250 3

C 2014 350 4

ALL_TEACHERS 2012 600 4

What's the best way to go about this?

Accepted Solutions

Solution

11-09-2016
07:18 AM

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

11-03-2016 02:29 PM

Do you actually have individual records for each teacher each year or is that "Total all teachers" actually the value you have in your data?

And are the 350 hours of teacher A included in the 600? Are you attempting to get a common value for the quartiles across years or use a different one per year?

If you have individual records then Proc Rank may do this.You didn't provide enough records to do much with quartiles so I made a few up.

```
data have;
input teacher $ school_year hours_spent_in_classroom;
datalines;
A 2012 350
B 2012 100
C 2012 150
D 2012 50
A 2013 150
B 2013 300
C 2013 100
D 2013 175
A 2014 50
B 2014 250
C 2014 350
D 2014 200
;
run;
proc sort data=have;
by school_year teacher;
run;
proc rank data=have out=want groups=4 descending;
by school_year;
var hours_spent_in_classroom;
ranks HourRanking;
run;
```

However if you do not individual hours for all of the teachers then this won't work.

Note that PROC rank has the first rank as 0. If you really want 1 then pass the want data set through a data step to add one to the ranks.

All Replies

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

11-03-2016 02:17 PM

PROC RANK GROUPS=4

Solution

11-09-2016
07:18 AM

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

11-03-2016 02:29 PM

Do you actually have individual records for each teacher each year or is that "Total all teachers" actually the value you have in your data?

And are the 350 hours of teacher A included in the 600? Are you attempting to get a common value for the quartiles across years or use a different one per year?

If you have individual records then Proc Rank may do this.You didn't provide enough records to do much with quartiles so I made a few up.

```
data have;
input teacher $ school_year hours_spent_in_classroom;
datalines;
A 2012 350
B 2012 100
C 2012 150
D 2012 50
A 2013 150
B 2013 300
C 2013 100
D 2013 175
A 2014 50
B 2014 250
C 2014 350
D 2014 200
;
run;
proc sort data=have;
by school_year teacher;
run;
proc rank data=have out=want groups=4 descending;
by school_year;
var hours_spent_in_classroom;
ranks HourRanking;
run;
```

However if you do not individual hours for all of the teachers then this won't work.

Note that PROC rank has the first rank as 0. If you really want 1 then pass the want data set through a data step to add one to the ranks.