how to calculate median for different variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 130
Accepted Solution

how to calculate median for different variables

I tried to calculate median of age and weight by city and by sex, the expected result is showed in the image. I wrote something like

data want;

set have;

by sex city;

median_age=median(age);

median_weight=median(weight);

run;

That doesn't work, looks like median() can't calculate numbers in a column, it works for numbers in the same row I think?

Thank you for your help.

have.jpg

Attachment

Accepted Solutions
Solution
‎12-27-2014 08:24 PM
Grand Advisor
Posts: 17,396

Re: how to calculate median for different variables

Go "old school" - use proc means and merge the results back in.

You can use the ways/types to control the different levels of output required.

Here's an example using SEX and WAYS 1 to get only the output by Sex levels.

proc means data=sashelp.class noprint;

class sex;

ways 1;

var weight age;

output out=med_vars median(weight)=med_weight median(age)=med_age;

run;

proc sql;

create table want as

select a.*, b.med_weight, b.med_age

from sashelp.class as a

left join med_vars as b

on a.sex=b.sex;

quit;


And an example using WAYS 2 with the CARS dataset


proc means data=sashelp.cars noprint;

class make type;

ways 2;

var mpg_city mpg_highway;

output out=med_vars2 median(mpg_city)=med_city median(mpg_highway)=med_highway;

run;

proc sql;

create table want as

select a.*, b.med_city, b.med_highway

from sashelp.cars as a

left join med_vars2 as b

on a.make=b.make

and a.type=b.type;

quit;

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: how to calculate median for different variables

/*If you have SAS 9.4, it is easy*/

proc sql;

     create table want as

           select *, median(age) as median_age, median(weight) as median_wt

                from sashelp.class;

quit;

/*If not, it will take some twists*/

data _null_;

     call symputx('nobs',nobs);

     stop;

     set sashelp.class nobs=nobs;

run;

data want_no94;

     do _n_=1 by 1 until (last);

           set sashelp.class (keep=age weight)end=last;

           array _age(&nobs) _temporary_;

           array _wt(&nobs) _temporary_;

           _age(_n_)=age;

           _wt(_n_)=weight;

     end;

     do _n_=1 by 1 until (last1);

           set sashelp.class end=last1;

           retain median_age median_wt;

           if _n_=1 then

                do;

                     median_age=median(of _age(*));

                     median_wt=median(of _wt(*));

                end;

           output;

     end;

run;

Frequent Contributor
Posts: 130

Re: how to calculate median for different variables

Thanks a lot for your reply! I have 9.3 version, so it is kind of headache.

I found that with your code, the resulted median is the median for the age or weight of whole data set.

So, how can I calculate the median by sex and by other variables at the same time if necessary? thanks!

Solution
‎12-27-2014 08:24 PM
Grand Advisor
Posts: 17,396

Re: how to calculate median for different variables

Go "old school" - use proc means and merge the results back in.

You can use the ways/types to control the different levels of output required.

Here's an example using SEX and WAYS 1 to get only the output by Sex levels.

proc means data=sashelp.class noprint;

class sex;

ways 1;

var weight age;

output out=med_vars median(weight)=med_weight median(age)=med_age;

run;

proc sql;

create table want as

select a.*, b.med_weight, b.med_age

from sashelp.class as a

left join med_vars as b

on a.sex=b.sex;

quit;


And an example using WAYS 2 with the CARS dataset


proc means data=sashelp.cars noprint;

class make type;

ways 2;

var mpg_city mpg_highway;

output out=med_vars2 median(mpg_city)=med_city median(mpg_highway)=med_highway;

run;

proc sql;

create table want as

select a.*, b.med_city, b.med_highway

from sashelp.cars as a

left join med_vars2 as b

on a.make=b.make

and a.type=b.type;

quit;

Frequent Contributor
Posts: 130

Re: how to calculate median for different variables

Thank you, Reeza! that works well.

Respected Advisor
Posts: 3,124

Re: how to calculate median for different variables

Sorry, I wasn't reading your question thoroughly enough. Here is the modified code. Well, if you have 9.3, then has already offered better options:

/*If you have SAS 9.4, it is easy*/

proc sql;

create table want as

           select *, median(age) as median_age, median(weight) as median_wt

                from sashelp.class

group by sex;

quit;

/*If not, it will take some twists*/

proc sql;

select max(ct) into :dim trimmed from

(select count(*) as ct from sashelp.class group by sex)

;quit;

proc sort data=sashelp.class out=class;

by sex;

run;

data want_no94;

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

           set class end=last;

              by sex;

           array _age(&dim) _temporary_;

           array _wt(&dim) _temporary_;

           _age(_n_)=age;

           _wt(_n_)=weight;

end;

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

           set class end=last1;

              by sex;

           retain median_age median_wt;

           if _n_=1 then

                do;

                     median_age=median(of _age(*));

                     median_wt=median(of _wt(*));

                end;

           output;

end;

run;

Frequent Contributor
Posts: 130

Re: how to calculate median for different variables

Thanks! that's also very helpful.

New Contributor
Posts: 3

Re: how to calculate median for different variables

Hi. It was really helpful this query.

Now I'm trying to do something similar, but calculating interquartile range instead of median. Is there any way to do that?

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 4358 views
  • 5 likes
  • 4 in conversation