how to calculate median for different variables

[Jonate_H]

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.

[Haikuo]

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

[Jonate_H]

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!

[Reeza · Accepted solution]

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;

[Jonate_H]

Thank you, Reeza! that works well.

[Haikuo]

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;

[Jonate_H]

Thanks! that's also very helpful.

[jfcubells]

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?