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

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Calculating IQR, Q1 and Q3 with a group by

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-07-2017 02:31 PM

Hello!

I'm trying to calculate Q1, Q3 and IQR to identify outliers from a dataset. The problem is that I need Q1, Q3 and IQR for each client and each product.

When I calculate the Median, I have no problems, but with the other measures it doesn't work the way I expect.

```
proc sql;
create table "Output" as
select *, median(quantity) as median, PCTL(25,quantity) as Q1, PCTL(75,quantity) as Q3, IQR(quantity) as IQR
from work.documentX
group by Client, Product;
quit;
```

I uploaded an example with an excel sheet.

Thanks a lot for you help!

Federico.

Accepted Solutions

Solution

06-13-2017
12:25 PM

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

Posted in reply to jfcubells

06-07-2017 03:05 PM

What version of SAS do you have?

AFAIK percentiles/median don't work in SQL until at least SAS 9.4 and I'm not even sure PCTLs work as well.

I would strongly suggest comparing results to PROC MEANS/UNIVARIATE at minimum.

You can see/use how I've done this here:

GitHub is where people build software. More than 28 million people use GitHub to discover, fork, and contribute to over 85 million projects.

All Replies

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

Posted in reply to jfcubells

06-07-2017 02:37 PM

PROC UNIVARIATE with a BY statement ought to give you the values you want

--

Paige Miller

Paige Miller

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

Posted in reply to jfcubells

06-07-2017 02:50 PM

Or proc means/summary requesting Q1, Q3 and Qrange with CLASS statement, or Proc Tabulate with the same statistics with the group by as class variables.

Solution

06-13-2017
12:25 PM

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

Posted in reply to jfcubells

06-07-2017 03:05 PM

What version of SAS do you have?

AFAIK percentiles/median don't work in SQL until at least SAS 9.4 and I'm not even sure PCTLs work as well.

I would strongly suggest comparing results to PROC MEANS/UNIVARIATE at minimum.

You can see/use how I've done this here:

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

Posted in reply to Reeza

06-13-2017 12:30 PM

Thanks a lot for all the help you all gave me.

The final solution was this one:

```
Proc MEANS Data=work.dataset
n median qrange p25 p75;
var Quantity;
class Client Product;
ods output summary=ranges;
run;
```