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
- /
- Quartiling and then finding the average in each qu...

- 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

01-06-2017 10:32 AM

Hi everyone,

I have a dataset with over 6000 observations. These observations are all rate ie 0.95, 0.83, 0.99 ext. I need to quartile the observations and then find the average rate in each quartile. Essentially I need the average rate in Q1, Q2, Q3, Q4. I tried various procedures and I couldn't do what I wanted. I thought I would ask this community!

Thanks

I have a dataset with over 6000 observations. These observations are all rate ie 0.95, 0.83, 0.99 ext. I need to quartile the observations and then find the average rate in each quartile. Essentially I need the average rate in Q1, Q2, Q3, Q4. I tried various procedures and I couldn't do what I wanted. I thought I would ask this community!

Thanks

Accepted Solutions

Solution

01-12-2017
07:32 AM

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

01-06-2017 11:10 AM - edited 01-06-2017 11:17 AM

How do you know which ones go to which group? At that rate how is it different than random? You definitely can't call it quartiles...

EDIT: My mistake, it won't matter since thats the group you're summarizing. But don't call it quartiles, that would be misleading.

```
proc sort data=sashelp.cars out=cars;
by mpg_highway;
run;
data check;
set cars nobs=num;
n_group=floor(_n_/(num/4));
if n_group=4 then n_group=3; *reassign last obs;
run;
proc means data=check;
class n_group;
var mpg_highway;
run;
```

All Replies

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

01-06-2017 10:47 AM

Use PROC RANK to form the quartiles, as shown in the article "Grouping observations based on quantiles."

Then use the CLASS statement in PROC MEANS to compute the statistics for each quantile, as shown in this example:

```
%let NumGroups = 4;
proc rank data=Sashelp.cars out=Want groups=&NumGroups ties=high;
var MSRP; /* variable on which to group */
ranks Group; /* name of variable to contain groups 0,1,...,k-1 */
run;
proc format; /* display 0-3 as Q1-Q3 */
value Quartile 0="Q1" 1="Q2" 2="Q3" 3="Q4";
run;
proc means data=Want N MEAN STD;
var MSRP;
class Group;
format group Quartile.;
run;
```

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

01-06-2017 10:54 AM

I hope next link will help you:

http://support.sas.com/resources/papers/proceedings10/135-2010.pdf

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

01-06-2017 11:08 AM

Hey! Thanks for the quick reply. I originally used proc rank and and split the observations into 4 groups. The problem I run into is that there are so many 100% observations that it groups them all together in Q1. For example. If I have 6000 observations, approx 40 percent of them will fall into the first group because they are all 100% rates. That leaves much less observations in Q2.

What I need is to evenly split the observations into equal groups and then quartile it. Basically some of the 100% rates will then fall into Q2, creating equal groups.

What I need is to evenly split the observations into equal groups and then quartile it. Basically some of the 100% rates will then fall into Q2, creating equal groups.

Solution

01-12-2017
07:32 AM

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

01-06-2017 11:10 AM - edited 01-06-2017 11:17 AM

How do you know which ones go to which group? At that rate how is it different than random? You definitely can't call it quartiles...

EDIT: My mistake, it won't matter since thats the group you're summarizing. But don't call it quartiles, that would be misleading.

```
proc sort data=sashelp.cars out=cars;
by mpg_highway;
run;
data check;
set cars nobs=num;
n_group=floor(_n_/(num/4));
if n_group=4 then n_group=3; *reassign last obs;
run;
proc means data=check;
class n_group;
var mpg_highway;
run;
```

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

01-06-2017 11:34 AM

Yes, unfortunately this problem can occur. See the article "Binning data by quantiles? Beware of rounded data." The article says that is there are N observations and k groups then "if there are more than *N/k* repeated values, the repeated value can occupy more than one quantile value. In fact, this will *always* happen if a particular value is repeated more than 2*N/k* times."

The article concludes with this warning: "Beware of using quantiles to bin rounded data into groups. Although the technique works great when almost all of the data values are distinct, you can run into problems if you ask for many bins and your data contain many repeated values."

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

01-06-2017 09:58 PM

If you want your ties to be randomly split between quartiles, use small random tie breakers:

```
data cars;
set sashelp.cars;
/* Add random noise smaller than data precision */
qtr_mpg_highway = mpg_highway + 0.00001*rand("uniform");
run;
proc rank data=cars out=check groups=4;
var qtr_mpg_highway;
run;
proc means data=check;
class qtr_mpg_highway;
var mpg_highway;
run;
```

PG

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

01-06-2017 11:50 AM

Hey, I am going go try this now. Just to clarify, what does the _n_ refer to below?

n_group=floor(_n_/(num/4));

if n_group=4 then n_group=3; *reassign last obs;

run;

n_group=floor(_n_/(num/4));

if n_group=4 then n_group=3; *reassign last obs;

run;

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

01-06-2017 11:55 AM

_n_ is a pseudo row number, that I'm using as a row number. It's an automatic variable.