topic Re: PROC SQL- MEDIAN FUNCTION in SAS Procedures

PROC SQL- MEDIAN FUNCTION

Benn — Sun, 30 Sep 2012 13:46:03 GMT

Hi Guys,

I am trying to obtain the Median using the PROC SQL function (basically because I need the variables of that particular observation to show up in the output as well, which PROC MEANS doesn't do so).

I have used the following code:

PROC SQL;

CREATE TABLE data2 AS SELECT*

FROM work.data1 GROUP BY DATE,R_PRICE

HAVING PRICE=MEDIAN(PRICE);

QUIT;

(I am trying to find the median given 2 conditions- DATE and R_PRICE)

However, the following error showed up,

WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING clause of the associated table-expression referenced a summary function."

Thanks a lot guys. Appreciate it.

Re: PROC SQL- MEDIAN FUNCTION

art297 — Sun, 30 Sep 2012 14:05:23 GMT

Median is a a row-wise not an aggregate function in proc sql. Take a look at:http://www.sascommunity.org/wiki/Fun_with_PROC_SQL_Summary_Functions

If you need to use median values, calculate them with proc means or proc summary, and merge the results with your original data before doing your proc sql run. However, do realize that with an even number of observations, it is quite possible that no record will contain the median value.

Re: PROC SQL- MEDIAN FUNCTION

Benn — Sun, 30 Sep 2012 14:12:43 GMT

Hi Arthur,

You are spot on for my current problem. I have been trying to merge my result back all day long, but could not do so. Until just now I manually checked both datasets and realized that the median did not turn up. And immediately I saw your reply and confirmed my suspicion.

Do you happen to know a way about this? Eg. choosing the larger of the 2 middle numbers and so on?

Thank Arthur!

Re: PROC SQL- MEDIAN FUNCTION

art297 — Sun, 30 Sep 2012 14:28:58 GMT

Not sure exactly what you want but, hopefully, the following example comes close:

proc summary data=sashelp.class median nway;

var age;

class sex;

output out=medians (drop=_:) median=median_age;

run;

proc sort data=sashelp.class out=class;

by sex;

run;

data need;

merge class medians;

by sex;

run;

proc sql;

create table test as

select *

from need

group by sex

having abs(age-median_age)=min(abs(age-median_age))

;

quit;

Re: PROC SQL- MEDIAN FUNCTION

Benn — Mon, 01 Oct 2012 15:53:18 GMT

Hi Arthur,

Thank you so much for your help. Your code works like a charm and offered me some much needed progress after 3 days.

Thanks again!

Re: PROC SQL- MEDIAN FUNCTION

BrunoMueller — Wed, 09 Apr 2014 14:58:05 GMT

FYI beginning with SAS9.4 MEDIAN is also an aggregate function and can be used with proc sql, see sample below. See also doc here SAS(R) 9.4 SQL Procedure User's Guide

proc sql; 
  create table sample as
  select
    make
    , model
    , invoice
  from
    sashelp.cars
  group by
    make
  having
    median(invoice) = invoice
  ;
quit;