Calcite | Level 5

## how to calculate average of a subset

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: how to calculate average of a subset

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

4 REPLIES 4
Opal | Level 21

## Re: how to calculate average of a subset

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

Super User

## Re: how to calculate average of a subset

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.

Calcite | Level 5

## Re: how to calculate average of a subset

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

Ammonite | Level 13

## Re: how to calculate average of a subset

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;``````
Discussion stats
• 4 replies
• 3199 views
• 1 like
• 4 in conversation