topic Q1,Q3 in proc sql in SAS Procedures

Q1,Q3 in proc sql

Hoz — Wed, 08 Feb 2012 03:10:23 GMT

I'd like to use median function in proc sq.

It was OK when i wrote down "proc sql; --- select median(x) ----".

But it was NG when "proc sql; --- select q3(x) ---".

I want to know to get Q1,Q3 in proc sql.

Thank you,

Hide

Q1,Q3 in proc sql

Pritish — Wed, 08 Feb 2012 03:39:41 GMT

My assumption Q1 and Q3 - Interquartile Range

Firstly, get the median of the variable:

proc sql;

select median(x) as M from tbl_nm;

quit;

Ex: M = 7500

Now that you have got your median you can find Q1 which is 25th Quantile.

proc sql;

select median(x) as Q1 where x <= 7500;

quit;

Next, go for Q3 which is 75th Quantile:

proc sql;

select median(x) as Q3 where x >= 7500

quit;

Is this what you are looking for?

Q1,Q3 in proc sql

Hoz — Wed, 08 Feb 2012 04:30:45 GMT

I see. It was the way to calculate Q1, Q3 with median function.

Thank you for your quick response, zinzuwadia.

Best regards,

Hide

Q1,Q3 in proc sql

Howles — Thu, 09 Feb 2012 02:40:48 GMT

Look at your results more carefully and you will see that PROC SQL does not support MEDIAN as a summary statistic. You can use the SAS MEDIAN function in PROC SQL, but with one argument all you get is the trivial result that the median of a single number is that number.

proc means median data=sashelp.class ;

var age ;

run ;

proc sql ;

select median(age) from sashelp.class ;

quit ;

Celko's SQL median has been implemented in SAS (http://www.sascommunity.org/wiki/Celko's_Median). That should get you started if you really have to do this in SQL.