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.
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;
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.
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!
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;
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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.