turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- how to calculate average of a subset

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-16-2017 06:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-16-2017 07:12 PM - edited 04-16-2017 07:21 PM

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

All Replies

Solution

04-17-2017
07:40 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-16-2017 07:12 PM - edited 04-16-2017 07:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-16-2017 08:36 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-17-2017 07:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-16-2017 08:37 PM

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;
```