BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Benn
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

5 REPLIES 5
art297
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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
SAS Super FREQ

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 86631 views
  • 9 likes
  • 3 in conversation