How to get the average of selected observations

Solved
Occasional Contributor
Posts: 17

How to get the average of selected observations

Hello All,

I am using SAS Enterprise Guide 4.3

I am attempting to average observations that are created due an assay ran in repeat. First, I tried to create a computed column and employed a conditional function. However, I getting an error message

This is an example of my dataset is below

detail 1 detail 2 detail 3    repeat

id=1    3.0      3.0       4.0          0

id=1    2.0      5.0       3.0          1

id=1    1.0      2.0       7.0          2

I am trying to average the two detail 1 observations that are associated with repeat 1 and 2.  No the the detail 1 with associated with the original run (repeat 0). I have multiple individuals (ids) so I want to selectively average their repeats as well without average them with others.

Can somebody walk me through how I would do so using SAS Enterprise.

Accepted Solutions
Solution
‎09-04-2013 03:53 PM
Super User
Posts: 13,583

Re: How to get the average of selected observations

One way would be Proc Means/summary

Proc Summary data=have nway;

where repeat>0;

class id visitnumber;

var detail1;

output out=detail1summary mean=detail1mean;

run;

This will calculate the mean of detail1 for each combination of ID and Visitnumber where repeat is greater than 0.

If you only want repeat 1 and 2,

where repeat in (1,2);

if you want to summarize other variable then add them to the VAR line and add names to

mean(detail1 detail2)=(detail1mean detail2mean)

I'm creating an output set in case you want to merge it back with original data.

All Replies
Super User
Posts: 23,773

Re: How to get the average of selected observations

1. What do you want your output to look like? An example based on your data is ideal.

2. So for ID1, Detail1 the average should be 1.5 or 2.0?

Occasional Contributor
Posts: 17

Re: How to get the average of selected observations

The average should be 1.5.

detail 1 detail 2 detail 3    repeat  detail1 avg

id=1    3.0      3.0       4.0          0           3.0

id=1    2.0      5.0       3.0          1           1.5

id=1    1.0      2.0       7.0          2           1.5

I guess it would be nice to have the average detail 1 associated with the individual.

Occasional Contributor
Posts: 17

Re: How to get the average of selected observations

I would essentially discard the original run. I just want the repeats averaged and associated with the individual and visit number (not found here) they were derived from

Super User
Posts: 23,773

Re: How to get the average of selected observations

In the filter and query tool:

1. Create a calculated column that groups observations into first and others by using repeat column. e.g. 32160 - How to Apply IF-THEN-ELSE Logic by Using SAS® Enterprise Guide

2. Create a new calculated column that is the avg of detail1

3. Add a group by condition and use your calculated column from step 1

4. Make sure to select all fields you want into your query.

Solution
‎09-04-2013 03:53 PM
Super User
Posts: 13,583

Re: How to get the average of selected observations

One way would be Proc Means/summary

Proc Summary data=have nway;

where repeat>0;

class id visitnumber;

var detail1;

output out=detail1summary mean=detail1mean;

run;

This will calculate the mean of detail1 for each combination of ID and Visitnumber where repeat is greater than 0.

If you only want repeat 1 and 2,

where repeat in (1,2);

if you want to summarize other variable then add them to the VAR line and add names to

mean(detail1 detail2)=(detail1mean detail2mean)

I'm creating an output set in case you want to merge it back with original data.

Occasional Contributor
Posts: 17

Re: How to get the average of selected observations

This is exactly what I want to do. However, Do I just place the code directly into SAS Enterprise Guide or should go to Base SAS.  I want to do all my work in one place.

Super User
Posts: 13,583

Re: How to get the average of selected observations

I don't use EG but I understand there is a place to put base sas code. I believethat there is a tool or task to summarize data that should allow you specify filter values (the where clause), grouping variables (the Class clause), the variables to summarize and direct the output.

Super Contributor
Posts: 308

Re: How to get the average of selected observations

In EG, you can work directly with SAS code (per ballardw) in a program object (File--> New --> Program). Alternatively, you can use a Task to generate the output (as Reeza has described).

Occasional Contributor
Posts: 17

Re: How to get the average of selected observations

I inserted this code with the proper alterations. However, when I assessed the output it was direct replication of detail 1.  It did not average the detail observation from repeat 1 and repeat 2. Is there something I am doing wrong.

Proc Summary data=WORK.QUERY_FOR_PT1 nway;

where repeat>0;

class uid visitno

algorithm

assaytyp

runorkit

repeat

detail 1

detail 2

detail 3

.........

var detail1

output out=detail1summary mean=detail1mean;

run;

Super User
Posts: 23,773

Re: How to get the average of selected observations

You have too many variables in the CLASS.

Occasional Contributor
Posts: 17

Re: How to get the average of selected observations

What is the maximum amount I can have?

Super User
Posts: 23,773

Re: How to get the average of selected observations

Sorry, I mis-typed.

The Class statement specifies how the calculation is grouped, think of it as the "GROUP BY" for SQL.

So if you have all your variables in there then your results will the unique value. I think you only want ID in the class statement.

Occasional Contributor
Posts: 17

Re: How to get the average of selected observations

That sounds good.  Because it is averaging the  observations there will be a repeat in averaged findings once I merge ( one less than before)---- How can I account for these repeated averaged observations in my analysis. Thanks to everybody for their help.

Occasional Contributor
Posts: 17

Re: How to get the average of selected observations

Dropped it down to four... and still no change and detail 1mean is still just a replicate of detail 1

🔒 This topic is solved and locked.