turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- how to calculate median for different variables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-27-2014 06:49 PM

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.

Accepted Solutions

Solution

12-27-2014
08:24 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-27-2014 08:24 PM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-27-2014 07:41 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-27-2014 08:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-27-2014 08:24 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-27-2014 09:38 PM

Thank you, Reeza! that works well.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-27-2014 10:53 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-27-2014 11:47 PM

Thanks! that's also very helpful.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-06-2017 11:48 AM

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?