topic Re: how to calculate median for different variables in SAS Programming

how to calculate median for different variables

Jonate_H — Sat, 27 Dec 2014 23:49:00 GMT

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.

Re: how to calculate median for different variables

Haikuo — Sun, 28 Dec 2014 00:41:40 GMT

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

Re: how to calculate median for different variables

Jonate_H — Sun, 28 Dec 2014 01:13:00 GMT

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!

Re: how to calculate median for different variables

Reeza — Sun, 28 Dec 2014 01:24:20 GMT

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;

Re: how to calculate median for different variables

Jonate_H — Sun, 28 Dec 2014 02:38:11 GMT

Thank you, Reeza! that works well.

Re: how to calculate median for different variables

Haikuo — Sun, 28 Dec 2014 03:53:51 GMT

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;

Re: how to calculate median for different variables

Jonate_H — Sun, 28 Dec 2014 04:47:43 GMT

Thanks! that's also very helpful.

Re: how to calculate median for different variables

jfcubells — Tue, 06 Jun 2017 15:48:17 GMT

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?