Calculating IQR, Q1 and Q3 with a group by

[jfcubells]

 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.

[PaigeMiller]

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

[ballardw]

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.

[Reeza · Accepted solution]

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

[jfcubells]

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;