how to calculate average of a subset

[vseshad]

Hello, 

I am pretty new to SAS and I need help in some basic calculations. I want to calculate the average of a subset of numbers in a column and divide every value of that column by the average.

For example, I have a table 

name  age

julia       45

julia       54

julia       34

julia       45

jason      23

jason     56

julia       23

jason     11

jason     34

julia       22

alec       31

alex       33

matt       28

jason     29

I want to calculate the average age of Jason and divide every value of age by the average. What is the easiest way to do this and is this something that can be done with proc sql? Thanks in advance for your help!

[art297 · Accepted solution]

You want to divide everyone's age by Jason's average age?

If so, here is one way:

data have;
  input name $  age;
  cards;
julia       45
julia       54
julia       34
julia       45
jason      23
jason     56
julia       23
jason     11
jason     34
julia       22
alec       31
alex       33
matt       28
jason     29
;

proc sql noprint;
  select mean(age)
    into :jage
      from have
        where name eq 'jason'
  ;
quit;

data want;
  set have;
  jzcore=age/&jage.;
run;

Art, CEO, AnalystFinder.com

[Reeza]

Your question is ambigous. 

Are you calculating an average per person and dividing each persons value divided by their individual average

OR

Are you calculating an average per person and dividing each persons value divided by the total average across all people

OR

By Jason's average as indicated?

Post sample data and sample output.

[vseshad]

I apologize if it seems ambiguous. I want to divide each person's value by Jason's average. 

[ChrisBrooks]

I think this should give you want you want

data have;
  input name $  age;
  cards;
julia       45
julia       54
julia       34
julia       45
jason      23
jason     56
julia       23
jason     11
jason     34
julia       22
alec       31
alex       33
matt       28
jason     29
;
run;

proc sql;
	create table want
	as select
	name,
	age,
	age/mean(age) as score
	from have
	group by name;
quit;