Help using Base SAS procedures

Calculate median

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

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
Respected Advisor
Posts: 3,124

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

View solution in original post


All Replies
Super User
Posts: 9,681

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
Respected Advisor
Posts: 3,124

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

Respected Advisor
Posts: 3,777

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.

Trusted Advisor
Posts: 1,204

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;

Respected Advisor
Posts: 3,777

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

Trusted Advisor
Posts: 1,204

Re: Calculate median in SQL

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 3706 views
  • 3 likes
  • 5 in conversation