topic Calculating IQR, Q1 and Q3 with a group by in SAS Enterprise Guide

Calculating IQR, Q1 and Q3 with a group by

jfcubells — Wed, 07 Jun 2017 18:31:46 GMT

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.

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

PaigeMiller — Wed, 07 Jun 2017 18:37:03 GMT

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

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

ballardw — Wed, 07 Jun 2017 18:50:49 GMT

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.

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

Reeza — Wed, 07 Jun 2017 19:05:03 GMT

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

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

jfcubells — Tue, 13 Jun 2017 16:30:13 GMT

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;