mean with proc sql

[Marzi]

I'm wondering why when I add any of the second means, the result is zero?!

data error;
input actual predicted;
datalines;
4   5 
6   6
9   8
10  10
4   4
6   8
4   4
7   9
8   8
7   5
;
run;
proc sql;
    select mean((mean(actual) - actual)**2) format 20.3 into :adjrsq from error;
quit;

sorry if my question is very simple to solve, I'm very beginner in SAS! 

[RW9]

What is it your trying to do?  The mean function will return one record, which is the mean, and that row will also contain the value which is the same.  So say 4 is the mean value of all that data, then the line your working on looks like this:

mean=4, actual=4

so:

mean((4 - 4)**2)

Will always return 0, as 4-4 is 0.

Maybe you want to merge mean onto the data so that you have all the data and mean:

proc sql;
  create table INTER as 
  select A.*,
           B.MEAN
  from   ERROR A
  left join (select mean(ACTUAL) as MEAN from ERROR) B
  on      1=1;
  create table WANT as
  select  mean((MEAN - ACTUAL)**2) 
  into :ADJRSQ 
  from INTER;
quit;

You could shrink the code above, but I am showing the interveening step. 

[Marzi]

Dear RW9;
Thanks for your comment, I used your guideline for some other purpose, was really useful.

[LinusH]

Just want to add that mean() with one argument will be executed as the SQL aggregate function (for all records, or per GROUP BY if specified).
With multiple arguments it's the SAS Language function that gets executed and will return the mean of the listed arguments (for each record).

[PGStats · Accepted solution]

I think what you want is:

proc sql;
    select mean(
        ((select mean(actual) from error) - actual)**2) format 20.3 
    into :adjrsq 
from error;
quit;

%put &adjrsq;

[Marzi]

Thanks PG Stats