Calculate median

[shixin]

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!

[Ksharp]

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

[Haikuo · Accepted solution]

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

[data_null__]

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

[shixin]

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.

[stat_sas]

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;

[data_null__]

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;

[stat_sas]

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