how to calculate average of a subset

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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!


Accepted Solutions
Solution
‎04-17-2017 07:40 AM
Esteemed Advisor
Posts: 7,290

Re: how to calculate average of a subset

[ Edited ]

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

 

View solution in original post


All Replies
Solution
‎04-17-2017 07:40 AM
Esteemed Advisor
Posts: 7,290

Re: how to calculate average of a subset

[ Edited ]

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

 

Grand Advisor
Posts: 17,325

Re: how to calculate average of a subset

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.

New Contributor
Posts: 2

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. 

Frequent Contributor
Posts: 117

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 162 views
  • 1 like
  • 4 in conversation