topic Re: Create a new dataset with average plot values calculated from individual data points in SAS Procedures

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

jchitw12 — Sat, 04 Nov 2017 16:07:50 GMT

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!

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

Reeza — Sat, 04 Nov 2017 16:10:05 GMT

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.

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

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

Reeza — Sat, 04 Nov 2017 16:11:59 GMT

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

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

jchitw12 — Sat, 04 Nov 2017 16:15:44 GMT

That did the trick. Thank you so much!!