Solved
New Contributor
Posts: 4

Create a new dataset with average plot values calculated from individual data points

Hello,

I have experiments with multiple plants that make up one plot, and plots are treated as experimental units within blocks. I typically use excel to calculate the mean of the plants within a plot, and then transfer those values to a new sheet to load into SAS and analyze. However, I want to use SAS to perform this step instead (SAS 9.4). For an example, this is a sample dataset with three plots (A, B, and C) each having 4 plants with a measurement (score). Then there are three blocks.

data sample;
input block line\$ plant score;
cards;
1 A 1 3
1 A 2 2
1 A 3 4
1 A 4 3
1 B 1 4
1 B 2 3
1 B 3 5
1 B 4 4
1 C 1 2
1 C 2 2
1 C 3 3
1 C 4 2
2 A 1 4
2 A 2 5
2 A 3 5
2 A 4 5
2 B 1 3
2 B 2 2
2 B 3 3
2 B 4 3
2 C 1 3
2 C 2 2
2 C 3 2
2 C 4 2
3 A 1 5
3 A 2 6
3 A 3 6
3 A 4 5
3 B 1 4
3 B 2 3
3 B 3 4
3 B 4 2
3 C 1 2
3 C 2 2
3 C 3 3
3 C 4 2
;
run;

So I want a dataset now with average values for each A, B, or C within each block that I can then use for analysis. I've found somewhat similar examples using proc sql or proc means, but I get errors when I try to implement. For example:

proc sql;
create table average as
select *, mean(score) as average_score
from sample
by block
where line between 'A' and 'A'
and plant between 1 and 4;
quit;

and this didn't work, the error message said ERROR 78-322: Expecting a ','. so I know I'm missing something.... this seems like such a simple thing, I know there must be an answer. Any help will be greatly appreciated!! Thank you so much!

Accepted Solutions
Solution
‎11-04-2017 12:14 PM
Super User
Posts: 23,754

Re: Create a new dataset with average plot values calculated from individual data points

proc sql;
create table average as
select *, mean(score) as average_score
from sample
group by block, line
;
quit;

All Replies
Super User
Posts: 23,754

Re: Create a new dataset with average plot values calculated from individual data points

Here are two fully worked methods, see if you can incorporate one with your data.

The error you state versus the code you've shown don't make sense. I would suggest posting the exact code and log.

Solution
‎11-04-2017 12:14 PM
Super User
Posts: 23,754

Re: Create a new dataset with average plot values calculated from individual data points

proc sql;
create table average as
select *, mean(score) as average_score
from sample
group by block, line
;
quit;

New Contributor
Posts: 4

Re: Create a new dataset with average plot values calculated from individual data points

That did the trick. Thank you so much!!

☑ This topic is solved.