Solved
Contributor
Posts: 22

# Calculate median

I am using SAS9.3 and was trying out the median function someone posted on this forum earlier on.

proc sql;

create table MEDIAN_SPEND as

SELECT GENDER

,AGE_GROUP

,SPEND

FROM FILE1

GROUP BY GENDER, AGE_GROUP

HAVING median(SPEND) = SPEND;

quit;

I still get the "The MEDIAN function has been called with only one argument." error message.

Can someone tell which part went wrong?

Thank you!

Accepted Solutions
Solution
‎09-26-2014 09:47 AM
Posts: 3,167

## Re: Calculate median in SQL

Upgrade to SAS 9.4 or to Use 2XDOW as below (not tested):

data want;

array spd (100) _temporary_;

do _n_=1 by 1 until (last.age_group);

set file1;

BY GENDER AGE_GROUP;

spd(_n_)=spend;

end;

median=median(of spd(*));

do _n_=1 by 1 until (last.age_group);

set file1;

BY GENDER AGE_GROUP;

if spend=median then

output;

end;

run;

Good luck,

Haikuo

All Replies
Super User
Posts: 10,761

## Re: Calculate median in SQL

If I remember right, You can only run MEDIAN() with sql under SAS9.4  .   But there are lots of workaround to get it , like array ,Hash Table ,merge ......

proc mean

Solution
‎09-26-2014 09:47 AM
Posts: 3,167

## Re: Calculate median in SQL

Upgrade to SAS 9.4 or to Use 2XDOW as below (not tested):

data want;

array spd (100) _temporary_;

do _n_=1 by 1 until (last.age_group);

set file1;

BY GENDER AGE_GROUP;

spd(_n_)=spend;

end;

median=median(of spd(*));

do _n_=1 by 1 until (last.age_group);

set file1;

BY GENDER AGE_GROUP;

if spend=median then

output;

end;

run;

Good luck,

Haikuo

Posts: 3,852

## Re: Calculate median in SQL

Are you most interested in calculating the median or using the function?

Contributor
Posts: 22

## Re: Calculate median in SQL

Calculating the median. I thought I could use the function in my SQL since it's much easier but I just learnt that it's only available in SAS9.4.

Posts: 1,270

## Re: Calculate median in SQL

proc means data=file1 median;
var spend;
class gender age_group;
output out=medians (where=(gender ne ' ' and age_group ne ' ') drop=_: ) median=median_spend;
run;

proc sql;

create table want as
select * from file1 a left join medians b
on a.gender=b.gender
and a.age_group=b.age_group
group by a.gender, a.age_group
having median_spend=spend;
quit;

Posts: 3,852

## Re: Calculate median in SQL

Isn't this "where=(gender ne ' ' and age_group ne ' ')" just removing the one-way tables and wouldn't it be easier to specify NWAY option in the PROC statement?

```stat@sas wrote:

proc means data=file1 median;  var spend;  class gender age_group;  output out=medians (where=(gender ne ' ' and age_group ne ' ') drop=_: ) median=median_spend;run;

proc sql;
create table want asselect * from file1 a left join medians bon a.gender=b.genderand a.age_group=b.age_groupgroup by a.gender, a.age_grouphaving median_spend=spend;quit;
```
Posts: 1,270