topic Median of one variable by values of another in SAS Programming

Median of one variable by values of another

Walternate — Mon, 01 Dec 2014 19:53:10 GMT

Hi,

I have data composed of an ID variable, a continuous variable (values 1-100), and a categorical variable (200 possible values), like this:

ID cont_var cat_var

1 30 1

2 25 2

3 42 2

4 97 1

5 55 1

6 12 2

The data are unique at the ID level.

What I want is to add a variable that summarizes the median of the cont_var by level of cat_var, like this:

ID cont_var cat_var new_var

1 30 1 55

2 25 2 25

3 42 2 25

4 97 1 55

5 55 1 55

6 12 2 25

I know how to do this by collapsing the data to levels of the categorical variable, but since I ultimately want to keep my dataset at the individual ID level, I'm hoping there's a way to do it without collapsing and rejoining.

Any help is much appreciated.

Re: Median of one variable by values of another

Haikuo — Mon, 01 Dec 2014 20:48:08 GMT

If you have 9.4, then:

data have;

input ID cont_var cat_var;

cards;

1 30 1

2 25 2

3 42 2

4 97 1

5 55 1

6 12 2

;

proc sql;

create table want as

select *, median(cont_var) as median from have group by cat_var

order by id;

quit;

Re: Median of one variable by values of another

ballardw — Mon, 01 Dec 2014 20:50:33 GMT

proc summary data=have nway;

class cat_var;

var cont_var;

output out= med median=;

run;

proc sql;

create table want as

select a.*, b.cont_var as new var

from have as a left join med as b on

a.cat_var=b.cat_var

order by ID;

quit;