topic Re: mean with proc sql in SAS Programming

mean with proc sql

Marzi — Thu, 27 Oct 2016 15:23:03 GMT

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!

Re: mean with proc sql

RW9 — Thu, 27 Oct 2016 15:42:51 GMT

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.

Re: mean with proc sql

LinusH — Thu, 27 Oct 2016 17:59:56 GMT

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).

Re: mean with proc sql

PGStats — Thu, 27 Oct 2016 18:53:27 GMT

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;

Re: mean with proc sql

Marzi — Fri, 28 Oct 2016 03:28:51 GMT

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

Re: mean with proc sql

Marzi — Fri, 28 Oct 2016 03:29:29 GMT

Thanks PG Stats