Solved
New Contributor
Posts: 3

# Calculating IQR, Q1 and Q3 with a group by

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
Super User
Posts: 23,937

## Re: Calculating IQR, Q1 and Q3 with a group by

Posted in reply to jfcubells

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:

https://gist.github.com/statgeek/31316a678433a1db8136

All Replies
Respected Advisor
Posts: 3,249

## Re: Calculating IQR, Q1 and Q3 with a group by

Posted in reply to jfcubells

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

--
Paige Miller
Super User
Posts: 13,876

## Re: Calculating IQR, Q1 and Q3 with a group by

Posted in reply to jfcubells

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
Super User
Posts: 23,937

## Re: Calculating IQR, Q1 and Q3 with a group by

Posted in reply to jfcubells

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:

https://gist.github.com/statgeek/31316a678433a1db8136

New Contributor
Posts: 3

## Re: Calculating IQR, Q1 and Q3 with a group by

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;``````
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 4 replies
• 2844 views
• 0 likes
• 4 in conversation