topic Re: Calculate median in SQL in SAS Procedures

Calculate median

shixin — Fri, 26 Sep 2014 13:35:58 GMT

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!

Re: Calculate median in SQL

Ksharp — Fri, 26 Sep 2014 13:43:37 GMT

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

Re: Calculate median in SQL

Haikuo — Fri, 26 Sep 2014 13:47:49 GMT

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

Re: Calculate median in SQL

data_null__ — Fri, 26 Sep 2014 13:55:18 GMT

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

Re: Calculate median in SQL

shixin — Fri, 26 Sep 2014 14:10:29 GMT

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.

Re: Calculate median in SQL

stat_sas — Fri, 26 Sep 2014 14:10:31 GMT

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;

Re: Calculate median in SQL

data_null__ — Fri, 26 Sep 2014 14:36:36 GMT

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 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;

Re: Calculate median in SQL

stat_sas — Fri, 26 Sep 2014 16:27:31 GMT

@data_null_: Thanks so much for the suggestion. Agreed, it is better to use NWAY instead of where clause.