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.
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:
PROC UNIVARIATE with a BY statement ought to give you the values you want
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.
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:
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.