PROC SQL- MEDIAN FUNCTION

[Benn]

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.

[art297]

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.

[Benn]

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!

[art297 · Accepted solution]

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;

[Benn]

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!

[BrunoMueller]

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;