Desktop productivity for business analysts and programmers

Calculating IQR, Q1 and Q3 with a group by

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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
Grand Advisor
Posts: 17,313

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

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,492

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

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

Grand Advisor
Posts: 10,196

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

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
Grand Advisor
Posts: 17,313

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

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
  • 144 views
  • 0 likes
  • 4 in conversation