topic Re: how to calculate average of a subset in SAS Programming

how to calculate average of a subset

vseshad — Sun, 16 Apr 2017 22:28:42 GMT

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!

Re: how to calculate average of a subset

art297 — Sun, 16 Apr 2017 23:21:31 GMT

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

Re: how to calculate average of a subset

Reeza — Mon, 17 Apr 2017 00:36:21 GMT

Your question is ambigous.

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

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

By Jason's average as indicated?

Post sample data and sample output.

Re: how to calculate average of a subset

ChrisBrooks — Mon, 17 Apr 2017 00:37:30 GMT

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;

Re: how to calculate average of a subset

vseshad — Mon, 17 Apr 2017 11:16:25 GMT

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