Help using Base SAS procedures

PROC SQL- MEDIAN FUNCTION

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

PROC SQL- MEDIAN FUNCTION

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.


Accepted Solutions
Solution
‎09-30-2012 10:28 AM
PROC Star
Posts: 7,356

Re: PROC SQL- MEDIAN FUNCTION

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=_Smiley Happy 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;

View solution in original post


All Replies
PROC Star
Posts: 7,356

Re: PROC SQL- MEDIAN FUNCTION

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.

Contributor
Posts: 35

Re: PROC SQL- MEDIAN FUNCTION

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!

Solution
‎09-30-2012 10:28 AM
PROC Star
Posts: 7,356

Re: PROC SQL- MEDIAN FUNCTION

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=_Smiley Happy 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;

Contributor
Posts: 35

Re: PROC SQL- MEDIAN FUNCTION

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!

SAS Super FREQ
Posts: 682

Re: PROC SQL- MEDIAN FUNCTION

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 38803 views
  • 5 likes
  • 3 in conversation